Last year and Previous Month Calculation

[khanashique]

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.

[pradosh_thakur]

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

[thannila]

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

[Gysbert_Wassenaar · Accepted solution]

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)

[NhanNguyen]

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

[khanashique]

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]

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

[khanashique]

Thanks Gysbert,

Master Calendar works...

Thanks a lot.