Dimensions

[upaliwije]

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

[PrashantSangle]

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

[upaliwije]

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)

[PrashantSangle]

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

[upaliwije]

Month Value is in string format

[PrashantSangle]

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

[upaliwije]

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

[tresesco]

If you could share your sample app, you would probably get a resolution faster.

[PrashantSangle · Accepted solution]

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

[upaliwije]

Thanks