Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a data table (cross table) with a large number of rows and multiple value fields per record/row. e.g.
Company,Account,Owner, Balance, Validated Balance, Debit Unvalidated Balance, CR Unvalidated Balance, Net Unvalidated Balance, P&L DR, P&L CR, P&L
1234, 123456789, PersonA, 1000, 500, 800,-300, 500, 15, -10, 5
I am trying to present this in a pivot table with:
Balance | Unvalidated Balances | P&L | |||||||
Company | Owner | Balance | % of Total Balance | Debit Unvalidated Balance | CR Unvalidated Balance | Net Unvalidated Balance | P&L DR | P&L CR | P&L |
1234 | PersonA | 1000 | 17% | 800 | -300 | 500 | 15 | -10 | 5 |
1234 | PersonB | 2000 | 33% | 0 | 0 | 0 | 0 | 0 | 0 |
1234 | PersonC | 3000 | 50% | 3000 | 0 | 3000 | 15 | -50 | -35 |
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.
Any ideas?
Hey Hi,
You can use text objects instead...
Or
make an inline of :
Load * Inline [
Heading, SubHeading
Balances, Balance
Balances, [% Of Total Balances]
......
];
Regards
Thanks Rahul,
My preference is not to use inline. Could you perhaps expand on what you mean by text objects?
Many thanks
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,
Hi jonwebster,
Try to use the variables to create a expresion and then use it on the straight table as a expression.
Anand