topic Re: Dimensions in QlikView

Dimensions

upaliwije — Wed, 26 Jun 2019 06:28:23 GMT

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

Re: Dimensions

PrashantSangle — Wed, 26 Jun 2019 06:36:20 GMT

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

Re: Dimensions

upaliwije — Wed, 26 Jun 2019 06:47:58 GMT

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

PrashantSangle — Wed, 26 Jun 2019 07:09:50 GMT

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

Re: Dimensions

upaliwije — Wed, 26 Jun 2019 07:32:46 GMT

Month Value is in string format

Re: Dimensions

PrashantSangle — Wed, 26 Jun 2019 10:56:45 GMT

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

Re: Dimensions

upaliwije — Fri, 28 Jun 2019 07:38:47 GMT

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

Re: Dimensions

tresB — Fri, 28 Jun 2019 07:49:26 GMT

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

Re: Dimensions

PrashantSangle — Fri, 28 Jun 2019 08:00:42 GMT

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

Re: Dimensions

upaliwije — Fri, 28 Jun 2019 08:15:21 GMT

Thanks