cancel
Showing results for 
Search instead for 
Did you mean: 

Notifications complaint rate report

Former Member
0 Kudos

hI,

I have a requirement as below

notification month equipment month(no. of notifications)

cy200601 cy200602 cy200603

1 1 3 1

2 2 ` 2 1

3 3 1 1

1- means notifications in 1st month after equipment built

2 - means notifications in 2nd month after equipment built

3 - means notifications in 3 rd month after equipment built

but what i am getting is

notification month equipment month(no. of notifications)

cy200601 cy200602 cy200603

cy200601 1 0 0

cy200602 2 ` 3 0

cy200603 3 2 1

cy200604 2 1 1

cy200605 6 4 1

whats the differenece is they want to see notification month as 1,2,3, means 1st 2nd 3rd months after equipment built so we need to design the query to calculate the number of failures according to their 1st 2nd 3rd months after it built

that means

for jan'08 1st built month will be jan'08

for feb'08 first built month would be feb'08 and so on so basically we need to get failure rates for first month after equipment built in 1st row and 2nd month in second so we nee dto get rid of the months prior to equipment built

why they need like this is because they want to do trending and forecasting on equipment build month versus failure rate according to number of months in service like

how the equipments performed in first 3 months after they built and how they performed after 6 months

if we can provide all those in one row like 1s month failures according to 1st week in service not according to what we are getting, if we built in april'08 we will be getting 0's in first 3 rows(because equipment not even built at that time) so that's why they ant to dispaly as shown in first screen.

one more issue is they want it in weeks,

and i have calweek asssigned to buth notification week and equipment but as we can use one time character i am getting notification week in my query and i need to fetch equipment week aswell.

Thanks for your time.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

You MAY be able to do this in Query Design by using a Formula variable, calculating number of days between notification data and build date. I did something very similar some years back (sorry, can't remember fine details!), but it took me quite a few attempts before I got the correct date formats, number formats etc so that I could subtract one from the other.

However, the better solution is to calculate this 'number of days' at time of data load, because then you have it in the DataProvider, from which point it is easy to report on it. However, you will probably tell me this is not an option as it is a DataProvider already working in Production...

Points?

Patrick

Former Member
0 Kudos

Hi Patrick,

Thanks for your response.

My cube is already in production, But i can do themodification over there. what user wants is in weeks and he wants to see for a year or more, even if we calculate age we need to create a structure for 52 weeks or so right? can we calculate age in bex directly do we need to use user exit for that.

is there any option to format in workboos by using macros or so. what would be the best feasinle option and how to acheive this.

Thanks,

Naveen

Former Member
0 Kudos

Well, a 52-week structure would certainly be one way of doing it (notifications date minus build date between 21 and 28 for week 4 etc). And using Formula variable (with replacement path, I think) should enable the subtraction to happen, always assuming your Notification is matched to the Material being built, of course.

Formatting etc in the workbook with vba is always possible, but my general advice is don't go there unless you absolutely have to... you end up spending LOTS of time maintaining vba code, just because you need to make a small change to a query.

Patrick

Former Member
0 Kudos

Hi Patrick,

Could you please explain a bit detail how can i acheive this, I am confused.

Thanks,

Naveen

Former Member
0 Kudos

I don't mind, but you will need to provide me much more detail as to what you have tried, and where you can't get it to work, complete with screenshots. Get my address from my profile.

Former Member
0 Kudos

I WILL SEND YOU ALL THE DETAILS.

Thanks,

Nav

Answers (0)