topic Re: Using Set Analysis to get Previous Month info in QlikView

Using Set Analysis to get Previous Month info

bimala0507 — Mon, 17 Aug 2015 07:47:07 GMT

Dear Experts:

By using Set Analysis I am trying to retrieve the Previous Month Sales Data. It is Dynamic, so based on user selection, it will find out the Current Month and Previous Month and display the Sales information

E.g. year Select 2015 , Month = 1,

It should show me the Value Jan-2015 Data and Dec-2014 sales data

Similarly if year Select 2015 , Month = 2

It should show me the Value Feb-2015 Data and Jan-2015 sales data

To my understanding both the below expression should work

sum({$<Inv_YearMonth_Num = {$(=Max(Date(Date#(max(Inv_YearMonth_Num),'YYYYMM')-1,'YYYYMM')))}>} Sales)

sum({$<InvYear = {$(=max(InvYear) - $(=if(max(InvMonth) = 1, 1, 0)))}, InvMonth = {$(=month(addmonths(max(Inv_YearMonth), -1)))}>} Sales)

Below are my Date and Month related field

InvYear	Inv_YearMonth	Inv_YearMonth_Num	InvMonth
2015	Feb-2015	201502	Feb
2015	Jan-2015	201501	Jan

Can anyone throw some light on this issue. The above expression evaluates to 0, which implies, it is not able to get the matching information.

Appreciate an immediate reply.

Kind Regards,

Bimala

Re: Using Set Analysis to get Previous Month info

sunny_talwar — Mon, 17 Aug 2015 07:58:59 GMT

Try this:

=Sum({$<Inv_YearMonth_Num = {"$(=Date(Date#(Max(Inv_YearMonth_Num),'YYYYMM')-1,'YYYYMM'))"}, InvMonth = , Inv_YearMonth = , InvYear = >} Sales)

The above expression should show you the sales for 201501 when 201502 is selected.

Re: Using Set Analysis to get Previous Month info

lironbaram — Mon, 17 Aug 2015 07:59:19 GMT

from my experience the best way to do previous months calculation

is to add to the calendar a monthstart field that conatians for each date the month first date

then your expressions will look like this

sum({<monthstart={"$(=max(monthstart))"}>} Sales)

and for pervious month

sum({<monthstart={"$(=addmonths(max(monthstart),-1))"},Inv_year=,Inv_Month=>} Sales)

the advantage of using addmonths function that it works between years automatically

Re: Using Set Analysis to get Previous Month info

teiswamsler — Mon, 17 Aug 2015 07:59:30 GMT

Hi Bimala

Try this: inset " " before and after calculation string

sum({$<Inv_YearMonth_Num = { " $(=Max(Date(Date#(max(Inv_YearMonth_Num),'YYYYMM')-1,'YYYYMM'))) " }>} Sales)

sum({$<InvYear = { " $(=max(InvYear) - $(=if(max(InvMonth) = 1, 1, 0))) " }, InvMonth = { " $(=month(addmonths(max(Inv_YearMonth), -1))) " }>} Sales)

Re: Using Set Analysis to get Previous Month info

qlikviewwizard — Mon, 17 Aug 2015 08:01:11 GMT

Hi Please use the below expression:

Sum({<Year=, Quarter=, Inv_YearMonth={'>=$(=max(Inv_YearMonth_Num)-1<=$(=max(Inv_YearMonth_Num))'}>} Sales)

Re: Using Set Analysis to get Previous Month info

sasiparupudi1 — Mon, 17 Aug 2015 08:09:19 GMT

sum({$<Inv_YearMonth_Num = {"$(=Date(addmonths(Date#(max(Inv_YearMonth_Num),'YYYYMM'),-1),'YYYYMM'))"}>} Sales)

Re: Using Set Analysis to get Previous Month info

jonathandienst — Mon, 17 Aug 2015 08:12:41 GMT

Something like this:

sum({$<Inv_YearMonth_Num = {"$(=Date(Max(Date#(Inv_YearMonth_Num,'YYYYMM')) - 1, 'YYYYMM'))"}>} Sales)

sum({$<Inv_YearMonth_Num = {"$(=Date(AddMonths(Max(Date#(Inv_YearMonth_Num,'YYYYMM')) - 1, -1), 'YYYYMM'))"}>} Sales)

Re: Using Set Analysis to get Previous Month info

jonathandienst — Mon, 17 Aug 2015 08:18:23 GMT

But it is much simpler if you create a proper QV date field when loading:

LOAD

...

Inv_YearMonth,

Date(Date#(Inv_YearMonth, 'MMM-YYYY')) As YMDate,

...

The expressions then are:

sum({$<YMDate = {"$(=Date(Max(YMDate) - 1))"}>} Sales)

sum({$<YMDate = {"$(=AddMonths(Max(YMDate) - 1, -1))"}>} Sales)

Re: Using Set Analysis to get Previous Month info

bimala0507 — Mon, 17 Aug 2015 08:59:09 GMT

Thanks so much for the insight.

I changed the original expression to below and it worked fine. I had to change to 1 instead of $ and put "". the below expression works fine.

sum({1<Inv_YearMonth_Num = {"$(=Date(Date#(max(Inv_YearMonth_Num),'YYYYMM')-1,'YYYYMM'))"}>} Sales)

Re: Using Set Analysis to get Previous Month info

Prink — Wed, 02 Jan 2019 09:18:07 GMT

I need to calculate sum(sales) - sum(sales) of prev month for 6 months in pivot table. Expression to calculate previous month, contains max() .due to which the max month is always selected. I want to get sum(sales) for previous months without using max(). Can u help me for this?