How to calculate CurrentYearMonth vs PrevYearMonth sales(Growth)

[#1]

Hi All,

I want to one report as shown below. In that everything is done except the growth. In growth what I want to do is, I want to calculate growth using the sale value of april,2010 to the sale value of april,2009.

When saying it looks simple but while doing finding it very difficult. Can some one please help me out.

If you feel passing the MonthName in the dimension is the problem then you can remove the MonthName and you can pass only the Month. I don't have issue with that. You can pass the month as well.

[suniljain]

Dear pls Check attached example . It will solve your problem.

[#3]

Hi Sunil,

Thanks for the document. I had verified the same document previously. But that is not what I was looking for.

I want the same thing for all the months of year without selecting the year and month. As per the document it works only if we select some values but in my case without doing any selection it should show the expected result for all the months.

Kindly check and let me know if you have such document.

[sunil2288]

Hi Rikab,

You can solve like this

Sum(if(InYearToDate(orderdate,maxorderdate,0),sales)) / Sum(if(InYearToDate(orderdate,maxorderdate,-1),sales))

Check it out. Hope it may solve your prblm

Thanks

Sunil

[#5]

Hi Sunil Panda,

Thanks for the reply!

Tell me according to my value which is there in the report. Then only it will be understandable.

This is what that is there in the report.

Dimension:

Area Manager

ItemName

=MonthName(TranDate)

Expression:

Sum(Qty) // Sales

Sum(Target_Qty) //Target

Sum(Qty)/Sum(Target_qty) //Acheivement

--????-- // Growth

[sunil2288]

Hi Rikab

Sum(if(InYearToDate(TranDate,MAX(TranDate),0),sales)) / Sum(if(InYearToDate(TranDate,MAX(TranDate),-1),sales))

Try this

Thanks

Sunil

[#7]

Hi Panda,

I am not getting any result even after using the expression that has been sent by you. All are showing null values only. Could you please check why it is happening so.

[sunil2288]

I don't have the data i can't check it out from my end..

[#9]

Hi All,

I have attached the sample application to work with. Hope some one can help me out.

[johnw]

One way is an AsOf table:

AsOfMonth, YearType, Month
Apr 2010, Current, Apr 2010
Apr 2010, Previous, Apr 2009
Mar 2010, Current, Mar 2010
Mar 2010, Previous, Mar 2009

You'll need a Month field in your data to make it work, but you should have one anyway, either directly or with a calendar. Then:

dimension 1 (left) = ZM
dimension 2 (left) = ItemName
dimension 3 (top) = AsOfMonth
Sales = sum({<YearType={'Current'}>} Qty)
Target = sum({<YearType={'Current'}>} Target_Qty)
Ach = Sales / Target
Growth = Sales / sum ({<YearType={'Previous'}>} Qty) - 1