Pivot Tables and Set Analysis

[Mav70]

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

[Mav70]

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.

[s_kabir_rab · Accepted solution]

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.

[Mav70]

Perfect. Thanks Kabir!