Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

upaliwije
Contributor 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

Re: Dimensions

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

Re: Dimensions

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
Contributor II

Re: Dimensions

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)

Re: Dimensions

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
Contributor II

Re: Dimensions

Month Value is in string format

Screenshot_1.png

Re: Dimensions

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
Contributor II

Re: Dimensions

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

 

MVP
MVP

Re: Dimensions

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

Re: Dimensions

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
Contributor II

Re: Dimensions

Thanks