I am trying to present this in a pivot table with:
Multiple Expressions for each value based on simple sums and counts/% - no problem with this
Headings to group the expressions into logical groups e.g. Balances (all metrics based on Balance), Unvalidated (all metrics using the unvalidated value fields), PL
% of Total Balance
Debit Unvalidated Balance
CR Unvalidated Balance
Net Unvalidated Balance
Question: How can I create the heading groups (i.e. Balance, Unvalidated Balances, P&L above)?
I considered turning the cross table into a straight table and applying a mapping to the value type to make this a stored value that could be used as dimension. However, in addition to wanting to avoid manual mappings using data not able to be extracted with the source data, the data volumes made it impossible as I ran out of RAM.
I have experiance doing this in Crystal mainly, but I have done it in Qlikview.
You values need to have a field that you can reference that will put it in each group. You would then create a formula that displays a text field if it falls in that group.
Here is an example I created to sort Order amounts by value:
If(orderamt<25000,'1. Under 25K',If((orderamt>=25000 and orderamt<50000),'2. 25K–50K',If((orderamt>=50000 and orderamt<100000),'3. 50K to 100K',If((orderamt>=100000 and orderamt<200000),'4. 100K to 200K',If((orderamt>=200000 and orderamt<300000),'5. 200K to 300K',If((orderamt>=300000 and orderamt<400000),'6. 300K to 400K',If((orderamt>=400000 and orderamt<500000),'7. 400K to 500K',if(orderamt>=500000,'8. Over 500K'))))))))as OrderLvl,