topic Re: Custom row expression in a Pivot Table in QlikView

Custom row expression in a Pivot Table

Mon, 28 Mar 2016 18:23:48 GMT

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

Re: Custom row expression in a Pivot Table

jlongoria — Mon, 28 Mar 2016 20:44:23 GMT

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

Gysbert_Wassenaar — Tue, 29 Mar 2016 06:09:24 GMT

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.

Re: Custom row expression in a Pivot Table

settu_periasamy — Tue, 29 Mar 2016 06:24:34 GMT

Hi,

May be something like this?

You can try with secondaryDimensionality function.

Sample attached..

Re: Custom row expression in a Pivot Table

Tue, 29 Mar 2016 20:49:50 GMT

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

Re: Custom row expression in a Pivot Table

oknotsen — Tue, 29 Mar 2016 20:53:25 GMT

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 .

Re: Custom row expression in a Pivot Table

Tue, 29 Mar 2016 20:56:23 GMT

How do I flag it?

Re: Custom row expression in a Pivot Table

Tue, 29 Mar 2016 21:02:54 GMT

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.