Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
=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]))
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)
For the Month, you can try using AddMonths(Month(Today()),-1).
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.