Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I have a problem with a pivot table
I have 3 fields and I want to add like this:
if(sum(Field 1)+Sum(Field 3) > Sum(field 2),sum(Field 1)+Sum(Field 3),Sum(field 2))
It work for dimensionality()=6 but for dimensionality()=1 it gets the greater of Sum(Field 1) + Sum(Field 3) or Sum(field 2) and not the sum of the values.
What can i do?
Best regards
Not quite clear - can you post an example.
In first instance would change the order of the aggregation and the condition, like SUM(IF ...)
++
Peter
Can you share you apllication , as it little difficult to undersatnd here 🙂
Dear Ashwin,
here you have a simple example
LOAD * INLINE [
F1, F2, F3,F4
1, 2, 6,D1
2, 1, 1,D2
];
Last field on total sum must be 9 and not 7
F4 | Sum (F1)+Sum (F2) | Sum (F3) | if(Sum (F1)+Sum (F2)>Sum(F3),Sum (F1)+Sum (F2),Sum(F3)) |
---|---|---|---|
D1 | 3 | 6 | 6 |
D2 | 3 | 1 | 3 |
Total | 6 | 7 | 7 |
you need to do an aggr function in pivot table
something like sum(aggr(if(Sum (F1)+Sum (F2)>Sum(F3),Sum (F1)+Sum (F2),Sum(F3)),F4))
Hi ,
As per your expression its showing 7. but when you will do sum of Row's in straight table. It will give to your result.
In pivote it will work with aggr function
please find attach application.