topic Re: Calculate sales for last N months in QlikView

Calculate sales for last N months

Mon, 26 Jan 2015 01:35:42 GMT

I have a variable created to calculate the number of months back from which I need to calculate the sales.I also have sales listed by months.

For example, if n is 6, I need to calculate the sales from july 2014.

I tried several expressions but unable to seem to get it right. Can someone please help ?

Re: Calculate sales for last N months

prabhuappu — Mon, 26 Jan 2015 05:00:14 GMT

Hi,

try the below expression,

sum({<orderdate={'>=$(vMonth)'}>}Sales)

there are couple of things you need to follow when using date in set analysis...

keep the date in number format when you are storing the value in variable. Otherwise it will consider it as string.

please upload a sample application if you couldn't achieve it

Regards,

Prabhu Appu

Re: Calculate sales for last N months

Mon, 26 Jan 2015 15:38:39 GMT

Hi Prabhu,

I tried your solution and did not work.I have a master calendar set up. The date is in yyyymm format. And the variable vMonth is an integer ( like 6 , if I need to calculate from six months ago)

Re: Calculate sales for last N months

saurabh5 — Mon, 26 Jan 2015 15:49:13 GMT

if n is a variable/field which selects your current month, then you can have an expression as below:

sum({<n={">=$(=max(n)-6)<=$(=max(n))"}>}sales)

regards

Saurabh

Re: Calculate sales for last N months

giakoum — Mon, 26 Jan 2015 15:52:27 GMT

see attached rolling months example. courtesy of Gysbert Wassenaar

Re: Calculate sales for last N months

Mon, 26 Jan 2015 16:12:30 GMT

Have you tried something along these lines?

=sum(if([DATEVALUE] > AddMonths(today(1),[VARIABLE]*-1),[SALESAMOUNT],0))

Re: Calculate sales for last N months

Mon, 26 Jan 2015 16:13:57 GMT

And if you want to include data from the start of that month, see below.

=sum(if([DATEVALUE] > monthstart(AddMonths(today(1),[VARIABLE]*-1)),[SALESAMOUNT],0))

Re: Calculate sales for last N months

prabhuappu — Mon, 26 Jan 2015 16:22:19 GMT

Hi Ravichandran,

Instead of storing month alone in a variable, store both YearMonth in it. You can use the below expression to Calculate YearMonth value of previous 6th month,

date(MonthStart(Date#('201205','YYYYMM'),-6),'YYYYMM')

Replace '201205' with your max(Date) value ($(=max(Date))). Since you have the date in YYYYMM format, max(Date) will return the current month or Max of month.

Use that variable in your set analysis as sum({<Date={'>=$(vMonth)'}>}Sales)

Please attach a sample, so that we can understand the problem that you are facing.

Regards,

Prabhu Appu

Re: Calculate sales for last N months

Mon, 26 Jan 2015 17:32:31 GMT

Thank you for helping me. I managed to get it work.

Re: Calculate sales for last N months

Wed, 24 Feb 2016 05:04:32 GMT

I use the following expression, but there is a problem, when I select Month=2, the result only show which belong to Month 2, not the rolling result.

vBegin=Num(date(Addmonths(Date#(Max(MonthKey),'YYYYMM'),-11),'YYYYMMDD'),'#,##0')

vEnd=Num(Date(Max(DateDate),'YYYYMMDD'),'#,##0')

Count({<Month={$(vCurrentMonth)},Key={"<=$(vEnd)>=$(vBegin)"}>}Machine)

Re: Calculate sales for last N months

Wed, 24 Feb 2016 05:05:08 GMT

I use the following expression, but there is a problem, when I select Month=2, the result only show which belong to Month 2, not the rolling result.

vBegin=Num(date(Addmonths(Date#(Max(MonthKey),'YYYYMM'),-11),'YYYYMMDD'),'#,##0')

vEnd=Num(Date(Max(DateDate),'YYYYMMDD'),'#,##0')

Count({<Month={$(vCurrentMonth)},Key={"<=$(vEnd)>=$(vBegin)"}>}Machine

Re: Calculate sales for last N months

prabhuappu — Thu, 03 Mar 2016 10:28:38 GMT

Hi,

Try changing the variable expression as below. Omitted the month selection from calculation.

vBegin=Num(date(Addmonths(Date#(Max({<Month=>}MonthKey),'YYYYMM'),-11),'YYYYMMDD'),'#,##0')

vEnd=Num(Date(Max({<Month=>}DateDate),'YYYYMMDD'),'#,##0')

Regards,

Prabhu Appu