Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bimala0507
Partner - Creator
Partner - Creator

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
sunny_talwar

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.

View solution in original post

9 Replies
sunny_talwar

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.

lironbaram
Partner - Master III
Partner - Master III

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

teiswamsler
Partner - Creator III
Partner - Creator III

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)

qlikviewwizard
Master II
Master II

Hi Please use the below expression:

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

sasiparupudi1
Master III
Master III

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

jonathandienst
Partner - Champion III
Partner - Champion III

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
jonathandienst
Partner - Champion III
Partner - Champion III

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
bimala0507
Partner - Creator
Partner - Creator
Author

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
Contributor III
Contributor III

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?