topic Sales selected months last year in QlikView

Sales selected months last year

annabergendal — Wed, 25 Nov 2015 20:56:48 GMT

How can I get sales for selected period last year?

If user selects 2015-03, how can I get sales for 2014-03?

See attached,

Thanks!

Re: Sales selected months last year

sunny_talwar — Wed, 25 Nov 2015 21:01:09 GMT

Try this expression:

=If(Sum(Sales) > 0, Above(Sum({<YeasrMonth=>}Sales)))

Re: Sales selected months last year

annabergendal — Thu, 26 Nov 2015 07:17:05 GMT

Thanks,

but that does not work.

It gives me previous months sales, not previous year current months sales.

Re: Sales selected months last year

Anonymous — Thu, 26 Nov 2015 07:23:58 GMT

For this kind of comparisons, I always calculate a "MonthID" in the script:

(num(Year(Date))-1)*12 + Month(Date) as MonthID

In your expressions you can use afterwards:

Sum({$<MonthID = {$(=Max(MonthID)-12)} >} Amount) for month in previous year

Sum({$<MonthID = {$(=Max(MonthID))} >} Amount) for month in current year

Hope this helps!!

Re: Sales selected months last year

tresB — Thu, 26 Nov 2015 07:24:27 GMT

=sum({<YeasrMonth={"$(=Left(YeasrMonth,4)-1&'-'&Right(YeasrMonth,2))"}>}Sales)

Re: Sales selected months last year

tamilarasu — Thu, 26 Nov 2015 07:41:57 GMT

Hi Anna,

Try,

sum({<YeasrMonth={"$(=Date(Addmonths(Date#(YeasrMonth,'YYYY-MM'),-12),'YYYY-MM'))"}>}Sales)

Re: Sales selected months last year

annabergendal — Thu, 26 Nov 2015 07:44:37 GMT

Thanks,

but none of these work for selection of several months.

Re: Sales selected months last year

Thu, 26 Nov 2015 07:48:21 GMT

try is

=SUM({<YearMonth={"$(=Date(Addmonths(Date#(YearMonth,'YYYY-MM'),-12),'YYYY-MM'))"}>}Sales)

Re: Sales selected months last year

Anonymous — Thu, 26 Nov 2015 07:49:00 GMT

try this:

At script level in your calender table, create this first:

Year*12 + num(Month(Date)) as MonthID

In your expressions you can use afterwards:

=Sum({<MonthID = {'=$(=Max(MonthID)-12)'} >} Amount) for month in previous year

=Sum({<MonthID = {'=$(=Max(MonthID))'} >} Amount) for month in current year

Hope this will help!!

Re: Sales selected months last year

tresB — Thu, 26 Nov 2015 08:05:46 GMT

For multiple months selection try like:

=sum({<YeasrMonth={$(vPreYrMonths)}>}Sales)

where,

vPreYrMonths =Concat( DISTINCT chr(39)&(Left(YeasrMonth,4)-1)&'-'&Right(YeasrMonth,2)&chr(39) , ',')

PFA

Re: Sales selected months last year

tamilarasu — Thu, 26 Nov 2015 08:09:08 GMT

Then Try,

sum({<YeasrMonth={$(=Concat(Chr(39)&Date(Addmonths(Date#(YeasrMonth,'YYYY-MM'),-12),'YYYY-MM')& Chr(39),','))}>}Sales)

Re: Sales selected months last year

annabergendal — Thu, 26 Nov 2015 08:34:31 GMT

Thanks tresesco and Tamil!

Your idea of defining an interval in the variable works!

Thanks to both of you, and also others who replied to this.

Anna