topic Re: How do I hide dimensions based on the expression value in a pivot table in QlikView

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

Anonymous — Wed, 01 Jul 2015 19:57:24 GMT

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

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

Qrishna — Wed, 01 Jul 2015 20:17:17 GMT

Hi Lisa,

Change your calculated dimension expression as

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

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

Anonymous — Wed, 01 Jul 2015 20:28:51 GMT

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!

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

Qrishna — Wed, 01 Jul 2015 21:05:56 GMT

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

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

Anonymous — Wed, 15 Jul 2015 14:30:13 GMT

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

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

Qrishna — Wed, 15 Jul 2015 15:23:43 GMT

Awesome.Please Close the thread as answered.

Thanks