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

[helen_pip]

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

[sunny_talwar]

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

[helen_pip]

Hi SunnyT

The Description is '01 All'

Kind Regards

Helen

[sunny_talwar]

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

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

[helen_pip]

Hi

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

[sunny_talwar]

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

[helen_pip]

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

[sunny_talwar]

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

[helen_pip]

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

[helen_pip]

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