Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Partner
Partner

Using Set Analysis to get Previous Month info

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


    

InvYearInv_YearMonthInv_YearMonth_NumInvMonth
2015Feb-2015201502Feb
2015Jan-2015201501Jan



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


1 Solution

Accepted Solutions

Re: Using Set Analysis to get Previous Month info

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.

9 Replies

Re: Using Set Analysis to get Previous Month info

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.

Partner
Partner

Re: Using Set Analysis to get Previous Month info

hi

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

Partner
Partner

Re: Using Set Analysis to get Previous Month info

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)

Arjunarao
Honored Contributor II

Re: Using Set Analysis to get Previous Month info

Hi Please use the below expression:

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

sasiparupudi1
Honored Contributor III

Re: Using Set Analysis to get Previous Month info

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

MVP
MVP

Re: Using Set Analysis to get Previous Month info

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP
MVP

Re: Using Set Analysis to get Previous Month info

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Partner
Partner

Re: Using Set Analysis to get Previous Month info

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)

Prink
New Contributor II

Re: Using Set Analysis to get Previous Month info

Hi

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?