Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
lvclifton
New Contributor II

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

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

Tags (1)
1 Solution

Accepted Solutions
krishna_2644
Valued Contributor III

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

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 : before.PNG

After :After.PNG

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

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

Thanks

krishna

5 Replies
krishna_2644
Valued Contributor III

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

Hi Lisa,

Change your calculated dimension expression  as

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

lvclifton
New Contributor II

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

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
Valued Contributor III

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

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 : before.PNG

After :After.PNG

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

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

Thanks

krishna

lvclifton
New Contributor II

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

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
Valued Contributor III

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

Awesome.Please Close the thread as answered.

Thanks

Community Browser