topic Re: Pivot Tables and Set Analysis in App Development

Pivot Tables and Set Analysis

Mav70 — Mon, 20 Dec 2021 21:08:46 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 from Qlik View ) I want to set 'Sum of Orders' to 'N/A' for 'Stage 2' where there is no data for 'Stage 1'. In this case, that would be for 'Client 3'.

Any assistance would be much appreciated.

Thanks in advamce,

Steve

Re: Pivot Tables and Set Analysis

Mav70 — Mon, 21 Sep 2020 14:27:20 GMT

I've found one solution to this by ordering the data by Client and Stage on load and setting the value to 'N/A' in the load script. Unfortunatley, this then stops the Total line being calculated so doesn't fulfill the requirements.

Re: Pivot Tables and Set Analysis

s_kabir_rab — Sat, 26 Sep 2020 13:17:51 GMT

Hi Steve,

best to resolve these in data model to avoid complex set analysis which can sometimes have a negative impact on a large application. I have just derived two new additional fields which I used in the pivot table to show how this can be achieved. This may not be the best approach but will give you an idea, and you can optimise it from that. But this will give you the desired outcome (see the screen shot). Hope this helps.

Re: Pivot Tables and Set Analysis

Mav70 — Mon, 28 Sep 2020 07:39:26 GMT

Perfect. Thanks Kabir!