Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
bimala0507
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
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.

View solution in original post

lironbaram
Partner
Partner

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
Partner

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

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

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
Partner

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?