5 Replies Latest reply: Mar 6, 2018 7:47 AM by Beck Bakytbek

Nullvalue in Aggr

Hi Folks,

i have a question:

my table does look like:

Budget               Invoice                     Sum                              Duduction ( 0,5 * Sum)

Sum            212816               242131,24               = -  29135,24                           0

Pos1   212816                  0

Pos2      0                       211530,387                 1285,62                              642,81

Pos3      0                        30600,86                     0

i am using the aggr-funct : Sum(aggr(if(Budget - Invoice <0, 0, Budget-Invoice)),MyDimension) it does work great,

but the Amount: 1282,62  (1282,62 * 0,5 = 642,81) will take into account.

how can i handle if my total Sum = 0, so then complete Deduction should have no values.

does anybody have any ideas?

Thanks a lot

Beck

• Re: Nullvalue in Aggr

Not sure if I understood it right... this doesn't works?:

if(Budget - Invoice <0, 0, Budget-Invoice)

Are 'Budget' and 'Invoice' field names or column names?

Or:

RangeMax(0, Sum(aggr(if(Budget - Invoice <0, 0, Budget-Invoice)),MyDimension))

• Re: Nullvalue in Aggr

Hi Ruben,

Bugdet and Invoice are column names

• Re: Nullvalue in Aggr

Hi Ruben,

what if, if the result of both fields: Bugdet and Invoice are positive, for instane

Budget                 Invoice               Deduction ( 50%)

Sum                  15000                    5000                   2500

on the base of above (your expression) i can exclude the values with minus, but what if, if i habe normal value, i mean whithout minus

• Re: Nullvalue in Aggr

Surely this is enough. I dont think the Aggr() is required as you are not nesting aggregations

=Sum(RangeMax(0, Budget - Invoice))

as expression and with MyDimension as the chart/table dimension

• Re: Nullvalue in Aggr

Hi Jonathan,

thanks a lot for your responce, i implemented  but it does not work