Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Friends,
I have a dimension field(Month_Year) in my pivot table. I have some calculation based on measures. (shown below)
here what I want is when I select a particular Month_Year in my list box preceding 12 months of the selected Month_Year should appear in the columns like shown above Eg. When I select Jun-2019 above months should be in columns. Since I am unable to modify the Dimension (Month_Year) to obtain the above output I kindly seek your help
just interchange < and >= in set analysis.
try below
sum({<TRN_DATE={">=$(=date(AddMonths(max(TRN_DATE),-5)))<=$(=date(max(TRN_DATE)))"}>}PREMIUM)
Regards,
Prashant Sangle
what is your expression? which date field you used
modify below expression as per your requirement.
try like
sum({<date={">=$(=date(max(date)))<$(=date(AddMonths(max(date),12)))"}>}Sales)
Regards,
Prashant Sangle
Thanks
I did as you suggested but it returns no value and only one column appear. My expression is shown below
sum({<Month_Year={">=$(=date(max(Month_Year)))<$(=date(AddMonths(max(Month_Year),12)))"}>}PREMIUM)
just check with date format.. use straight table and move your cursor to expression header and check what value it is evaluating.
is it matching with your date format which you have month_year field?
Month_Year is in date format of string format?
Regards,
Prashant Sangle
Month Value is in string format
then convert that month_year value in date format in back end and use that field in set analysis for comparision
try below step
1: add new field as Date(Date#(Month_Year,'current month year format')) as date_month_year
2: in set analysis use this date_month_year field
Regards,
Prashant Sangle
I have tried with your suggestion got following result
My expression is
sum({<TRN_DATE={">=$(=date(max(TRN_DATE)))<$(=date(AddMonths(max(TRN_DATE),5)))"}>}PREMIUM)
But what I want is past 5 month from max TRN_DATE. I changed the expression as shown below but desired output is not showing
sum({<TRN_DATE={">=$(=date(max(TRN_DATE)))<$(=date(AddMonths(max(TRN_DATE),-5)))"}>}PREMIUM)
Kindly help me to correct the expression
If you could share your sample app, you would probably get a resolution faster.
just interchange < and >= in set analysis.
try below
sum({<TRN_DATE={">=$(=date(AddMonths(max(TRN_DATE),-5)))<=$(=date(max(TRN_DATE)))"}>}PREMIUM)
Regards,
Prashant Sangle
Thanks