How do I hide dimensions based on the expression value in a pivot table

[Anonymous]

I have a pivot table with 3 dimensions and one expression.  The expression is:

MinString(If(CertTypeName = CertTypeRequired, 'Certified', 'Missing'))

I want to hide the one of the dimensions if the expression returns 'Certified' and display all dimensions if the expression returns 'Missing'.

At this time, I have a calculated dimension of:

if(MinString(If(CertTypeName = CertTypeRequired, 'Certified', 'Missing')) = 'Missing'), CertTypeRequired)

but I get an //error in calculated dimension.

I've tried using this expression as a conditional expression for the dimension - no luck.

I want a list of just Missing (not null) values.

Thanks in advance for your assistance!!  (The pivot table is on the SC Summary Tables tab.  I have two copies of the same table while I work on it.)

Lisa

[krishna_2644]

Hi Lisa,

Change your calculated dimension expression  as

=Aggr(MinString(If(CertTypeName = CertTypeRequired, 'Certified', 'Missing')),RankAbbrev,FULLNAME)

[Anonymous]

Thank you Krishna!  I applied the expression and it resulted in the dimension providing "Certified" or "Missing" text.  It did not hide the rows with "Certified" as the expression result.

Did you try this and it worked?  I used it in my third dimension and it removed the Certification name and replaced with "Certified" or "Missing" in the Dimension Column.

Thank you so much for trying to assist me!

[krishna_2644 · Accepted solution]

Lisa,

So you want to see only the rows which got a status of 'missing'. iF so

Then Please add the calculated  dimension

=If(

    Aggr(MinString(If(CertTypeName = CertTypeRequired, 'Certified', 'Missing')),RankAbbrev,FULLNAME,CertTypeRequired) <> 'Certified',

    Aggr(MinString(If(CertTypeName = CertTypeRequired, 'Certified', 'Missing')),RankAbbrev,FULLNAME,CertTypeRequired),

    Null()

  ) 

and check 'Suppress when value is null' for the calculated dimension.

then you would having something like below screenshots.

Before :

After :

If you are expecting the same result then let me know,

i Can edit your chart expression and will attach the file.

Thanks

krishna

[Anonymous]

Sorry to take so long to respond, but I got locked out of Qlik Community until they fixed my account.

Thank you so much!  That did work.

Just as an FYI and I'll try to follow up...but, when we publish my app to the Accesspoint we use, we cannot see these two tables!  I will be posting on that issue separately (now that I can log in again!)

Thank you again!!

Lisa

[krishna_2644]

Awesome.Please Close the thread as answered.

Thanks