Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Custom row expression in a Pivot Table

Hello,

I have a Pivot Table where the last Dimension (active?) has Yes or No values.  I moved this field so it is the last 2 columns of my Pivot.  The expression is a sum of hours.  The pivot works great but now the customer would like to see the value of NO/YES as the last column.  I can't figure out a way to do this.  Any ideas?  I tried searching this forum and others before asking.  Any advice would be appreciated.

Below is an image of what I am trying to do...

help.JPG

1 Solution

Accepted Solutions

Re: Custom row expression in a Pivot Table

The easiest solution is, as Joe says above, to use three expressions

  • sum({<active={'Yes'}>}hours)
  • sum({<active={'No'}>}hours)
  • sum({<active={'No'}>}hours)/sum({<active={'Yes'}>}hours)

Make sure to use the correct case sensitive field names.


talk is cheap, supply exceeds demand
7 Replies
jlongoria
New Contributor III

Re: Custom row expression in a Pivot Table

From what you state/show I believe you have count([active?]) as the expression and are including [active?] as one of the dimensions. If not, please attach sample qvw.

One alternative (depending on what you need) is to define a 'Yes' and 'No' expression using set analysis: count({<[active?]={"Yes"}>} [active?]) for the 'Yes' expression

count({<[active?]={"No"}>} [active?]) for the 'No' expression

You can then create a 3rd expression as [No]/[Yes] and set the number format accordingly.

Re: Custom row expression in a Pivot Table

The easiest solution is, as Joe says above, to use three expressions

  • sum({<active={'Yes'}>}hours)
  • sum({<active={'No'}>}hours)
  • sum({<active={'No'}>}hours)/sum({<active={'Yes'}>}hours)

Make sure to use the correct case sensitive field names.


talk is cheap, supply exceeds demand

Re: Custom row expression in a Pivot Table

Hi,

May be something like this?

Capture.JPG

You can try with secondaryDimensionality function.

Sample attached..

Not applicable

Re: Custom row expression in a Pivot Table

You are my hero.  That worked perfectly.  I have never used Set Analysis but will need to start reading up on it.

oknotsen
Honored Contributor III

Re: Custom row expression in a Pivot Table

If your question is now answered, please flag the Correct Answer.

If not, please make clear what part of your question you still need help with .

May you live in interesting times!
Not applicable

Re: Custom row expression in a Pivot Table

How do I flag it?

Not applicable

Re: Custom row expression in a Pivot Table

I don't see the Correct Answer button?  Qlik Community Tip: Marking Replies as Correct or Helpful  Sorry, I am brand new to actually posting on this site.

Community Browser