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.

[#2]

Hi John,

Thanks for your suggestions. Do you want to manually create a INLINE table and load the below table and later I need to changes in dimension and expression as said by you above.

Please clear my doubt as I am still not clear with it.

AsOf table:
AsOfMonth, YearType, Month (Instead of Month need to pass Trans_Month as it is a month field in my data)
Apr 2010, Current, Apr 2010
Apr 2010, Previous, Apr 2009
Mar 2010, Current, Mar 2010
Mar 2010, Previous, Mar 2009

[#3]

Hi John,

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

This is not forming linkage between Dates of source data and Month created in above table.

Can you share sample QV?

Thanks.

[#4]

Hi,

I've tried to make a report. Look attached.

The idea is the same as described before: You create a table of current and previous months. What is more, add a new field "TranMonthName" in FACT table.

[llauses243]

Hi Rikab,

All must be simple ...

Tell me if this is the solution ...otherwise excuse me

I does attach .qvw made step by step

good luck, Luis

[sunil2288]

Hi Rikab

Try This

Sum(if(InYearToDate(timestamp(TranDate,'YYYY-MM-DD'),YearStart(timestamp(TranDate,'YYYY-MM-DD')),0),Qty))
/
Sum(if(InYearToDate(timestamp(TranDate,'YYYY-MM-DD'),YearStart(timestamp(TranDate,'YYYY-MM-DD')),-1),Qty))

While using this i can't understand why YearStart function is not working .. it Should work ..

If it works then your query is solved

Thanks

Sunil

[#7]

Hi Panda,

Thanks for your help. I tried the same here but it doesn't seems to be working. In all the records it is showing null values in all the records of this column.

[llauses243]

Hi Rikab,

Is it correct for a item ?

good luck, Luis

[#9]

Hi Luis,

Exactly! That is correct value. But the rule is I want only in Pivot table only(As show in my report which is there in the application). Could you please do the required changes and send it to me.

[#10]

Hi Luis,

It is little urgent could you please send it at the earliest.

Thanks in advance!

[#11]

Hi Rikab

another solution is to store the past and the current at the same place (date) in the table.

if it can help do it like this

there is already a table named SH with all the data

Evol_src:
LOAD distinct

makedate(SH_An + 1, SH_Mois) as SH_Date,

SH_Etab,
SH_CA as SH_CAp
RESIDENT SH
WHERE date(makedate(SH_An, SH_Mois), 'YYYYMM') <= date( makedate(mid($(PeriodeData), 1, 4) - 1, num(mid($(PeriodeData), 5, 2))), 'YYYYMM')
;

JOIN (SH)
LOAD distinct
SH_Date,
SH_Etab,
SH_CAp
RESIDENT Evol_src
;

drop table Evol_src;

in my case PeriodeData means the departure time.

best regards

christian