topic Re: Sum deeper than filter in App Development

Sum deeper than filter

tnunes — Thu, 16 May 2024 22:43:05 GMT

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.

Re: Sum deeper than filter

Lech_Miszkiewicz — Thu, 16 May 2024 23:38:05 GMT

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)

Re: Sum deeper than filter

TauseefKhan — Fri, 17 May 2024 04:04:03 GMT

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.**

Re: Sum deeper than filter

Vegar — Fri, 17 May 2024 04:44:48 GMT

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

];

Re: Sum deeper than filter

tnunes — Fri, 17 May 2024 09:34:46 GMT

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.

Re: Sum deeper than filter

Firefly_cam — Fri, 17 May 2024 16:43:25 GMT

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))