# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Contributor II

## Last year and Previous Month Calculation

Hi Experts,

I need to get the value of Last Year's Previous Month's data, how can i get, please advise.

I have tried the following but it does not work.

if(isnull(Year),
Sum({<Year = {"(=Year(Today())-1)"},Month = {"(=Month(Today())-1)"}
>}([AMT1]+[AMT2])*[PR1]),
Sum({<Year = {"\$(=getfieldselections(Year))"},
Month = {"\$(=getfieldselections(Month))"}
>}([AMT1]+[AMT2])*[PR1]))

Thanks,

MAK.

Labels (3)

• ### set_analysis

1 Solution

Accepted Solutions
MVP & Luminary

## Re: Last year and Previous Month Calculation

For cases like this it's a good idea to create a field in the load script that contains serial values that increase by one for each consecutive month. Create a Master Calendar table and in that table create that serial month field.

MasterCalendar:
Date, Month(Date) as Month, AutoNumber(MonthStart(Date)) as _MonthSerial  … ;
Load Date(MinDate+iterno()) as Date While iterno() <= MaxDate - MinDate ;
Load Min(Date)-1 as MinDate, Max(Date) as MaxDate resident TransactionTable

Then you can use the _MonthSerial field in your set analysis expressions:
Current month: sum({<Year, Month, _MonthSerial={\$(=Max(_MonthSerial))}>}Amount)
Previous month: sum({<Year, Month, _MonthSerial={\$(=Max(_MonthSerial)-1)}>}Amount)

talk is cheap, supply exceeds demand
7 Replies
Honored Contributor II

## Re: Last year and Previous Month Calculation

if(isnull(Year),
Sum({<Year = {"\$(=Year(Today())-1)"},Month = {"\$(=Month(Today())-1)"}
>}([AMT1]+[AMT2])*[PR1]),
Sum({<Year = {"\$(=getfieldselections(Year))"},
Month = {"\$(=getfieldselections(Month))"}
>}([AMT1]+[AMT2])*[PR1]))
Learning never stops.
Contributor

MVP & Luminary

## Re: Last year and Previous Month Calculation

For cases like this it's a good idea to create a field in the load script that contains serial values that increase by one for each consecutive month. Create a Master Calendar table and in that table create that serial month field.

MasterCalendar:
Date, Month(Date) as Month, AutoNumber(MonthStart(Date)) as _MonthSerial  … ;
Load Date(MinDate+iterno()) as Date While iterno() <= MaxDate - MinDate ;
Load Min(Date)-1 as MinDate, Max(Date) as MaxDate resident TransactionTable

Then you can use the _MonthSerial field in your set analysis expressions:
Current month: sum({<Year, Month, _MonthSerial={\$(=Max(_MonthSerial))}>}Amount)
Previous month: sum({<Year, Month, _MonthSerial={\$(=Max(_MonthSerial)-1)}>}Amount)

talk is cheap, supply exceeds demand
New Contributor III

## Re: Last year and Previous Month Calculation

For the Month, you can try using  AddMonths(Month(Today()),-1).

Contributor II

## Re: Last year and Previous Month Calculation

Hi,

Thanks for your reply and i really appreciate your time and effort, the expression  which you have provided  earlier does not work, below is the expression i have tried, just have a look at it.

if(isnull(Year),
Sum({<Year = {"\$(=Year(Today())-1)"},
Month = {"\$(=Month(YearEnd(today()))-1+1)"}
>}([AMT1]+[AMT2])*[PR1]),
Sum({<Year = {"\$(=getfieldselections(Year))"},
Month = {"\$(=getfieldselections(Month))"}
>}([AMT1]+[AMT2])*[PR1]))

Above expression works partly, the second part (selection) works on selections but if the selection is null it does not return the value expected.

First part of the expression works fine separately but when i join it with second part then it returns 0.

Sum({<Year = {"\$(=Year(Today())-1)"},
Month = {"\$(=Month(YearEnd(today()))-1+1)"}
>}([AMT1]+[AMT2])*[PR1])

Thanks,

MAK.

Honored Contributor II

## Re: Last year and Previous Month Calculation

Please try the approach Gysbert suggested

or Modify the expression to
if(getselectedcount(Year),
Sum({<Year = {"\$(=Year(Today())-1)"},
Month = {"\$(=Month(YearEnd(today()))-1+1)"}
>}([AMT1]+[AMT2])*[PR1]),
Sum({<Year = {"\$(=getfieldselections(Year))"},
Month = {"\$(=getfieldselections(Month))"}
>}([AMT1]+[AMT2])*[PR1]))
Learning never stops.
Contributor II

## Re: Last year and Previous Month Calculation

Thanks Gysbert,

Master Calendar works...

Thanks a lot.