Custom row expression in a Pivot Table

[#1]

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

[jlongoria]

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.

[Gysbert_Wassenaar · Accepted solution]

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.

[settu_periasamy]

Hi,

May be something like this?

You can try with secondaryDimensionality function.

Sample attached..

[#5]

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

[oknotsen]

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 .

[#7]

How do I flag it?

[#8]

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.