# New to QlikView

Discussion board where members can get started with QlikView.

Highlighted 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

 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

1 Solution

Accepted Solutions MVP

## 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 MVP

## 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

## 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

## 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)

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)

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

## 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

## 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

## 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)

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?