Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
upaliwije
Creator II
Creator II

Dimensions

Dear Friends,

I have a dimension field(Month_Year) in my pivot table. I have some calculation based on measures. (shown below)

Screenshot_1.png

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

1 Solution

Accepted Solutions
PrashantSangle

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

 

 

Great dreamer's dreams never fulfilled, they are always transcended.

View solution in original post

9 Replies
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

Great dreamer's dreams never fulfilled, they are always transcended.
upaliwije
Creator II
Creator II
Author

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

Great dreamer's dreams never fulfilled, they are always transcended.
upaliwije
Creator II
Creator II
Author

Month Value is in string format

Screenshot_1.png

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

Great dreamer's dreams never fulfilled, they are always transcended.
upaliwije
Creator II
Creator II
Author

I have tried with your suggestion got following result

Screenshot_1.png

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
MVP
MVP

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

PrashantSangle

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

 

 

Great dreamer's dreams never fulfilled, they are always transcended.

View solution in original post

upaliwije
Creator II
Creator II
Author

Thanks