cancel
Showing results for 
Search instead for 
Did you mean: 

Calculalting Average over period of 2 months

Former Member
0 Kudos

Hi all,

In my cube, i am having the monthly availablity % of an equipment.

In the query i need the number of equipments with availability > x% over the period of two months.

The user will enter the year and the report will display the number of equipments in each month, with the average availability > x% over two months period, ie for February it should display the no. of equipments with average availability > x% in January & February, and so on.

How can I achieve it?

Any help would be appreciated.

Thanks & Regards,

Ankit

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Ankit,

In general, you can create additional column with average calculation and then apply condition build on this column.

But, as far as I understood, you need to show for January average for January and December of the previous year, for February - average for February and January, for March - average for March and February etc. What if one of these averages for different months will comply with your condition, and the others - will not? What the report should show?

Best regards,

EUgene

Former Member
0 Kudos

Hi Eugene,

Yes, your understanding is correct. In report i have to display the number of equipments with the average greater than x%.

How do i create additional column with average calculation, can you explain?

Regards,

Ankit

Former Member
0 Kudos

Hi Ankit,

Your key figurea are in the structure, right?

Right click on the structure - new formula, double click on '(', then on January KF, then on '+', then on February KF, then on')', then on '/', then on '2'.

Do the same for each month.

Then you can create a condition on this calculated KFs.

The problem is - if one of this average is greater than a x%, and another one (for another month) is lower than x%, should you show this record or not?

Best regards,

Eugene

Former Member
0 Kudos

Hi Eugene,

In my cube, I have the key figure availability which stores data consolidated on a basis of particular CALMONTH values.

My problem is, I have a data for particular month, how to get the data of previous month to calculate the average.

Regards,

Ankit

Former Member
0 Kudos

I usually create a BEx variable for a reporting month. I add a new selection into the structure, put there a KF, restrict CALMONTH by variable entered. Then add another selection and put there the same, but use a variable offset = -1 for a previous month.

Best regards,

Eugene

Former Member
0 Kudos

Hi,

I have done the same thing for many of my reports.

But in this particular case, the report is a yearly report. The only user entery is the year. Is there any way to get the value of 0CALMONTH in the variable.

Regards,

Ankit

Former Member
0 Kudos

One of the ways: create a customer exit and populate it from the entered year - add '01' or '12' to YYYY.

Best regards,

Eugene