Calculating revenue across two dimensions

[frederikanderse]

Hi guys

I have an applications with two customer dimensions. The invoiced customer and the actual customer. I am trying to sum up the total revenue of both of these in a single straight table, but am however unable to make it work. (See sample in attachment)

Data set:

Customer 1	Customer 2	Revenue
A	A	1
A	B	2
B	A	10
B	B	20

Output table:

Customer 2	Revenue Customer 1	Revenue Customer 2
A	3	11
B	30	22

Fx: The "Revenue Customer 1" for row 1 in "Customer 2" is calculated: 1 + 2 = 3.

Any ideas?

Best regards

Frederik

[Gysbert_Wassenaar]

See attached qvw for two options. I recommend modifying your data in the script and use that data in a pivot table. If you want to do things the hard way use the valuelist option in the lower straight table.

[frederikanderse]

Hi Gysbert

Thanks for your solutions - great ideas!

Pivot table:

Good solution, however not possible due to the amounts of data. (+100 mill. rows)

ValueList:

Clever solution, but not applicable with +100K customers.

Best regards

Frederik

[Gysbert_Wassenaar]

Well, you could use a straight table instead of a pivot table using two expressions like

sum({<V2.Group={'Revenue Customer 1'}>}V2.Revenue)

sum({<V2.Group={'Revenue Customer 2'}>}V2.Revenue)

But nobody is going to look at +100K customers in one list, never mind 100+ million records in one chart object. You may want to make sure that some (direct or indirect) selection of customers is made by users before the chart is rendered.

You may also have to aggregate data in the script first to get decent performance.