topic Re: Subtract values for rolling months in QlikView

Subtract values for rolling months

Thu, 09 Aug 2012 13:24:57 GMT

Hi,

I'm looking to subtract values from the current month with the Previous Year's same month, as in (May 2012 - May 2011) for a rolling period of 1 year. That is, the current month is May 2012, and its dynamic; I'm using a variable to capture both the dates but I'm not able to get the values for May 2011. Similarly I need the same for the previous 12 months. I'm using these values to plot a line graph to find the Trend.

The series am looking for:

(May 2012 - May 2011), (Apr 2012 - Apr 2011) and so on, for 12 values.

Please help me out!

Thanks & Regards,

Krishna

Re: Subtract values for rolling months

giakoum — Thu, 09 Aug 2012 13:37:47 GMT

Sum(Sales)

Sum({<MonthYear={"$(=AddMonths(MonthYear,-12))>} Sales)

assuming that MonthYear is your month field and Sales the value you are after.

Then limit the chart values to 12.

Re: Subtract values for rolling months

Tue, 14 Aug 2012 07:46:22 GMT

Hi,

Thanks for your reply.

But the expression is not working, is there anything to do with the double quotes put before the dollar symbol? It treates the expression after the double quotes as field and when I remove it, its like a variable.

Sum(Sales) - Sum({<MonthYear={"$(=AddMonths(MonthYear,-12))>} Sales)

Thanks,

Krishna

Re: Subtract values for rolling months

giakoum — Tue, 14 Aug 2012 08:00:13 GMT

true I forgot to close them. Sorry!

Sum(Sales) - Sum({<MonthYear={"$(=AddMonths(MonthYear,-12))"}>} Sales)

a sample application would help if the syntax is still incorect.

Re: Subtract values for rolling months

Tue, 14 Aug 2012 11:22:55 GMT

Hi Loannis,

The above expression is not working............

We need to have YEAR OVER YEAR Volume for 12 months,

For instance,

Jan12 Vol- Jan11Vol, Feb12Vol- Feb12Vol...........................

Thanx