topic Re: Pivot table not behaving as expected - based on constraints of expression in QlikView

Pivot table not behaving as expected - based on constraints of expression

helen_pip — Wed, 02 Nov 2016 08:31:02 GMT

Dear Qlikview user

I have created a pivot table where the dimension is Month Year and I also have a calculated dimension: =$(='['& only($(=vUser)) & ']')

which displays a field which the user selects

v_user = GetFieldSelections($Field)

At the moment I have a field called Description selected and therefore my dimension is displaying data by MonthYear and Description

In my expression I have put a restriction where I want to show just information for Description 1 :

if(Activity = 9999, '-',sum({-<Missing_Date_Flag = {'*'}> * <Variance_Days = {'<= 0'}, Description = {'01) All'}>}Activity))

I am expecting my pivot table to just show data for Description 01, however all the descriptions are being displayed in my pivot table

Could someone kindly help me with why my expression is ignoring my command to just show Description '01) All'

I have tried this technique elsewhere and the theory works, so I am assuming there is something wrong with my expression

Any help would be greatly appreciated

Kind Regards

Helen

Re: Pivot table not behaving as expected - based on constraints of expression

sunny_talwar — Wed, 02 Nov 2016 08:34:09 GMT

Does description contain 01 or 01) All?

if(Activity = 9999, '-',sum({-<Missing_Date_Flag = {'*'}> * <Variance_Days = {'<= 0'}, Description = {'01) All'}>}Activity))

Does the portion in red needs to be just '01'?

if(Activity = 9999, '-',sum({-<Missing_Date_Flag = {'*'}> * <Variance_Days = {'<= 0'}, Description = {'01'}>}Activity))

Re: Pivot table not behaving as expected - based on constraints of expression

helen_pip — Wed, 02 Nov 2016 08:36:34 GMT

Hi SunnyT

The Description is '01 All'

Kind Regards

Helen

Re: Pivot table not behaving as expected - based on constraints of expression

sunny_talwar — Wed, 02 Nov 2016 08:42:32 GMT

So this isn't working? Without the closing parenthesis?

if(Activity = 9999, '-',sum({-<Missing_Date_Flag = {'*'}> * <Variance_Days = {'<= 0'}, Description = {'01 All'}>}Activity))

Re: Pivot table not behaving as expected - based on constraints of expression

helen_pip — Wed, 02 Nov 2016 08:51:18 GMT

Unfortunately not! I also had a synthetic key, which I thought would solve the issue, but with no avail

At least I know now that the expression is not the issue. It may be something to do with my data load

I just wanted to check that the expression was written accurately, so thank you for confirming this

Kind Regards

Helen

Re: Pivot table not behaving as expected - based on constraints of expression

sunny_talwar — Wed, 02 Nov 2016 08:54:02 GMT

I mean the only other thing you can try is may be try this (although I am 100% sure if that will make any difference):

if(Activity = 9999, '-',sum({-<Missing_Date_Flag = {'*'}, Description = {'01 All'}> * <Variance_Days = {'<= 0'}, Description = {'01 All'}>}Activity))

Re: Pivot table not behaving as expected - based on constraints of expression

helen_pip — Wed, 02 Nov 2016 15:50:18 GMT

Helo SunnyT

Thank your response earlier, you theory certainly worked for one of my expressions.

However I am getting a problem with the equation below, where it appears as though it is ignoring my request and not selecting just description '01 ALL'

Could you kindly take a look at my expression to see how I can get it to work to just show Description '01 All'

=if(Activity = 9999, '-',sum({$-<Missing_Date_Flag={'*'}, Description = {'01) All'}>}Activity))

Kind Regards

Helen

Re: Pivot table not behaving as expected - based on constraints of expression

sunny_talwar — Wed, 02 Nov 2016 15:54:47 GMT

Why are you adding ) in your 01) All? I am not sure I understand if it is 01 All or 01) All?

=if(Activity = 9999, '-',sum({$-<Missing_Date_Flag={'*'}, Description = {'01) All'}>}Activity))

Re: Pivot table not behaving as expected - based on constraints of expression

helen_pip — Wed, 02 Nov 2016 16:00:21 GMT

Hello

The bracket '01) All' is just part of the values within the Field and is derived from a field in a system that I have no control over. It is part of the text of the field value

I.e. 01) All

02) Spinal

03) Trauma

04) Cancer

Kind Regards

Helen

Re: Pivot table not behaving as expected - based on constraints of expression

helen_pip — Wed, 02 Nov 2016 16:05:24 GMT

Hello SunnyT

I have an expression which works for me: I took out the set analysis and used If instead

=if(Activity = 9999, '-',sum({$-<Missing_Date_Flag={'*'}>} If(Description = '01) All',Activity)))

Thanks for your help anyway