topic Re: Pivot Tables and Set Analysis in QlikView

Pivot Tables and Set Analysis

Mav70 — Fri, 15 Nov 2024 23:57:30 GMT

Hi.

Am hoping someone can help. I have some data that is split by Client, then Stage then some measures. I'm trying to set a particular measure in a pivot table for 'Stage 2'to 'N/A' where there is no data for that client in 'Stage 1'.

I've attached an example with sample data (as I can't provide the real data).

In the pivot table shown above (and in the qvw attached) I want to set 'Sum of Orders' to 'N/A' for 'Stage 2' where there is no data for 'Stage 1'. In thise case, that would be for 'Client 3'.

Any assistance would be much appreciated.

Thanks in advamce,

Steve

Re: Pivot Tables and Set Analysis

chrismarlow — Mon, 21 Sep 2020 19:01:49 GMT

Hi,

If you are only going to display your pivot table as shown then have you thought about using a straight table instead? You lose the ability to do roll-ups/partial sums, that you don't seem to need, but it is much easier to do logic between columns like you appear to be trying to do.

Cheers,

Chris.

Re: Pivot Tables and Set Analysis

sunny_talwar — Mon, 21 Sep 2020 19:13:25 GMT

Try this

Sum({$<Client = p({<[Stage] = {'Stage 1'}>})>} [No. Orders]) Sum({$<Client = p({<[Stage] = {'Stage 1'}>})>} [No. Contacts])

Re: Pivot Tables and Set Analysis

Mav70 — Mon, 21 Sep 2020 20:05:06 GMT

Thanks Sunny. Unfortunately I still need Client 3 to show but with 'Sum of Orders' to be N/A and 'Sum of Contacts' to be calculated as 5.

Re: Pivot Tables and Set Analysis

Mav70 — Mon, 21 Sep 2020 20:03:42 GMT

Thanks Chris. Unfortunately I need to have Stage 1 and Stage 2 as columns with each measure as sub-columns as in a Pivot.

Re: Pivot Tables and Set Analysis

Brett_Bleess — Wed, 14 Oct 2020 14:46:26 GMT

Things would need to follow @sunny_talwar approach I believe in regard to Set Analysis, but if you want to keep that Client 3 line, you would need an If statement in Stage 2 using the Set Analysis to set the NA etc. I believe is the correct solution... I am no Set Analysis guru though, so not much help on adjusting Sunny's expressions to fit the bill here, but I wanted to toss out the approach, believe that should work. Sunny may circle back, it appears we may still be having some notification issues in Community posts letting folks know there have been subsequent posts etc...

Regards,
Brett