Summing different values depnding from value of grouping dimensions

[fabio]

Hi guys,

i' sorry for the enigmatic subject 🙂

an example is the best thing!

Data Model:

tab:
load * inline [
productType, price1, price2, price3
productType1, 4, 6, 3
productType2, 3, 7, 12
productType1, 6, 15, 3
productType2, 2, 17, 22
];

i'd like to build a table/graph grouping by productType and summing the prices like this:

productType1, 95

productType2, 5

where for the product1 i sum(price1+price2+price3) but for product2 i sum just price 1.

It is possible?

Any idea?

many thanks!!!

[johngouws]

In the real data, how many product types are there? 

[fabio]

Very few...

4 or 5..

[johngouws]

May be oversimplified, but try something like this:

tab:
load * inline [
productType, price1, price2, price3
productType1, 4, 6, 3
productType2, 3, 7, 12
productType1, 6, 15, 3
productType2, 2, 17, 22
];

NoConcatenate

table:
LOAD
*,
if(productType='productType1',price1+price2+price3) as productType1_Price,
if(productType='productType2',price1) as productType2_Price
Resident tab;
Drop Table tab;

The in the app you sum productType1_Price or productType2_Price

[fabio]

thanks but does not work becouse in the record "productType1" you have to consider the price2 and price3 of the productType2 also..

it is not simple..

[fabio · Accepted solution]

eureka!

maybe i found the solution

an expr like this:

=SUM({<productType = {'productType1'}>}aggr(sum(total price2 + price3), productType))+ sum(price1)

it seems to work!

i need to test it better, but i'm confident