Sum deeper than filter

[tnunes]

Hi community, 

I need to create a sum with a specific critéria. 

Table example:

A | B | C | D

1 | 1 | 4 | 3

1 | 2 | 7 | 2

1 | 3 | 7 | 20

Result needed:

A | R

1 | 6

Result is calculated with the following condition: if (C-D > 0) then C-D else 0, so the "aux" table should be : 

A | B | C - D with condition

1 | 1 | 1

1 | 2 | 5

1 | 3 | 0

And I need to sum.

[Lech_Miszkiewicz]

HI @tnunes 

does your test if (C-D > 0) then C-D else 0 have to be done on each row level separatelly? 

If yes - your bet is to do it in load script during data load as that will yield much better performance:

If(C-D > 0, C-D, 0) as E

then in UI you just do Sum(E)

[TauseefKhan]

Hello @tnunes 

Result needed:     

Script:

Table:
LOAD * INLINE [
A, B, C, D
1, 1, 4, 3
1, 2, 7, 2
1, 3, 7, 20
];

// Create calculated column
TempTable:
LOAD *,
If(C-D > 0, C-D, 0) as [C-D_with_condition]
RESIDENT Table;

// Create new table with calculated column
ResultTable:
LOAD A,
Sum([C-D_with_condition]) as R
RESIDENT TempTable
GROUP BY A;

**If the Above solution is correct/appropriate replies as "solution". Please LIKE threads if the provided solution is helpful to.**

[Vegar]

An alternative to the if() statement suggested by others is to use rangemax(), like this.

Table:

LOAD *, rangemax(C-D, 0) as R INLINE [

A, B, C, D

1, 1, 4, 3

1, 2, 7, 2

1, 3, 7, 20

];

[tnunes]

Hi, 

Thanks for all the replies. 

I think using this in data load I will not have the result that I want since it depends on all filters. 

I am trying to use something like "Sum(Aggr(Sum()))" , so Sum all the C-D, agreggate and Sum all to get A result.

[Firefly_cam]

Load * inline [
A | B | C | D

1 | 1 | 4 | 3

1 | 2 | 7 | 2

1 | 3 | 7 | 20] (delimiter is '|');

Measure would be:

Sum((C-D)*fabs(C>D))