Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
khanashique
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)
1 Solution

Accepted Solutions
MVP & Luminary
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:
Load
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
pradosh_thakur
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.
thannila
Contributor

Re: Last year and Previous Month Calculation

=if(isnull(year),sum({<Year={"$(=Max(Year)-1)"}>}([AMT1]+[AMT2])*[PR1]),sum({<Date={">=$(=num(AddMonths(MonthStart(max(Date)),-1)))<=$(=num(AddMonths(MonthEnd(max(Date)),-1)))"},Month=>}([AMT1]+[AMT2])*[PR1]))

MVP & Luminary
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:
Load
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
NhanNguyen
New Contributor III

Re: Last year and Previous Month Calculation

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

khanashique
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])

 

Need your kind assistance.

 

Thanks,

MAK.

pradosh_thakur
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.
khanashique
Contributor II

Re: Last year and Previous Month Calculation

Thanks Gysbert,

Master Calendar works...

Thanks a lot.