Value (Expression) Field Groups

[#1]

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:

• 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

		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?

[rahulgupta]

Hey Hi,

You can use text objects instead...

Or

make an inline of :

Load * Inline [

Heading, SubHeading

Balances, Balance

Balances, [% Of Total Balances]

......

];

Regards

[#3]

Thanks Rahul,

My preference is not to use inline. Could you perhaps expand on what you mean by text objects?

Many thanks

[#4]

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,

[its_anandrjs]

Hi jonwebster,

Try to use the variables to create a expresion and then use it on the straight table as a expression.

Anand