5 Replies Latest reply: Aug 21, 2012 3:15 PM by suresh kumar chandrasekaran

Calculating the average usage for last 3 months

Currently Being Moderated

Hi Experts,

Need your help and suggestions for the below scenario:

I have a report with the fields: Customer, Average Usage in last 3 months and Usage in Current month.

Filter is on Month/Year. Month/Year field is a string at DB level.

Can you please suggest how to calculate the average usage for the last 3 months by taking the user input for the current Month/Year.

Regards,

George

• Re: Calculating the average usage for last 3 months
Currently Being Moderated

Hi George,

Since the report is giving the current month data. Also Month/Year prompt is placed on DB end. So you can calculate the last three month average in backend based on the user prompt.

Thanks

Suresh

• Re: Calculating the average usage for last 3 months
Currently Being Moderated

Hi Suresh,

The filter is at report level. My requirement is that based on the selection on the Month/Year Filter(its a single filter with Month name and Year) need to calculate the previous 3 months average.

Regards,

George

• Re: Calculating the average usage for last 3 months
Currently Being Moderated

Hi George,

Since the report is collecting only one month data. Now you can't get the previous three months information. Please let me know the DB information. If you are using the BEX as backend, you can create the prompt variable in Bex and you cal calculate the last three month average.

Thanks

Suresh

• Re: Calculating the average usage for last 3 months
Currently Being Moderated

Hi Suresh,

I am using IDT on top of Hana. So is there any way it can be accomplished at report level or at IDT level. I am using only report level filter and till query level entire data is available.

Regards,

George

• Re: Calculating the average usage for last 3 months
Currently Being Moderated

Hi George,

In this situation, you have to collect the data for entire fiscal year. Here you should have to use "Fiscal Year" and "Month/Year" prompts in "OR" operator. Now the report will collect one complete fiscal year data. Here the disadvantage is performance and need to apply current month filter for required measures.

Then you can filter the measure to current "Month/Year". Also you can calculate the previous three months average. I hope, this will resolve your issue.

Thanks

Suresh