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

[chrismarlow]

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.

[sunny_talwar]

Try this

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

[Mav70]

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.

[Mav70]

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

[Brett_Bleess]

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