topic How to calculate CurrentYearMonth vs PrevYearMonth sales(Growth) in QlikView

How to calculate CurrentYearMonth vs PrevYearMonth sales(Growth)

Tue, 05 Oct 2010 11:31:38 GMT

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.

How to calculate CurrentYearMonth vs PrevYearMonth sales(Growth)

suniljain — Tue, 05 Oct 2010 11:58:49 GMT

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

How to calculate CurrentYearMonth vs PrevYearMonth sales(Growth)

Tue, 05 Oct 2010 12:30:25 GMT

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.

How to calculate CurrentYearMonth vs PrevYearMonth sales(Growth)

sunil2288 — Tue, 05 Oct 2010 12:40:38 GMT

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

How to calculate CurrentYearMonth vs PrevYearMonth sales(Growth)

Tue, 05 Oct 2010 12:49:34 GMT

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

How to calculate CurrentYearMonth vs PrevYearMonth sales(Growth)

sunil2288 — Tue, 05 Oct 2010 13:02:13 GMT

Hi Rikab

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

Try this

Thanks

Sunil

How to calculate CurrentYearMonth vs PrevYearMonth sales(Growth)

Tue, 05 Oct 2010 13:17:57 GMT

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.

How to calculate CurrentYearMonth vs PrevYearMonth sales(Growth)

sunil2288 — Tue, 05 Oct 2010 13:31:53 GMT

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

How to calculate CurrentYearMonth vs PrevYearMonth sales(Growth)

Tue, 05 Oct 2010 13:46:39 GMT

Hi All,

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

How to calculate CurrentYearMonth vs PrevYearMonth sales(Growth)

johnw — Tue, 05 Oct 2010 23:10:43 GMT

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

How to calculate CurrentYearMonth vs PrevYearMonth sales(Growth)

Wed, 06 Oct 2010 05:36:26 GMT

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

How to calculate CurrentYearMonth vs PrevYearMonth sales(Growth)

Wed, 06 Oct 2010 06:21:40 GMT

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.

How to calculate CurrentYearMonth vs PrevYearMonth sales(Growth)

Wed, 06 Oct 2010 13:51:29 GMT

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.

How to calculate CurrentYearMonth vs PrevYearMonth sales(Growth)

llauses243 — Wed, 06 Oct 2010 21:22:04 GMT

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

How to calculate CurrentYearMonth vs PrevYearMonth sales(Growth)

sunil2288 — Thu, 07 Oct 2010 08:03:39 GMT

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

How to calculate CurrentYearMonth vs PrevYearMonth sales(Growth)

Thu, 07 Oct 2010 10:20:54 GMT

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.

How to calculate CurrentYearMonth vs PrevYearMonth sales(Growth)

llauses243 — Thu, 07 Oct 2010 11:19:05 GMT

Hi Rikab,

Is it correct for a item ?

good luck, Luis

How to calculate CurrentYearMonth vs PrevYearMonth sales(Growth)

Thu, 07 Oct 2010 12:18:57 GMT

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.

How to calculate CurrentYearMonth vs PrevYearMonth sales(Growth)

Thu, 07 Oct 2010 14:28:31 GMT

Hi Luis,

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

Thanks in advance!

How to calculate CurrentYearMonth vs PrevYearMonth sales(Growth)

Thu, 07 Oct 2010 14:37:38 GMT

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