Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

beck_bakytbek
Valued Contributor III

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

5 Replies

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

beck_bakytbek
Valued Contributor III

Re: Nullvalue in Aggr

Hi Ruben,

Bugdet and Invoice are column names

beck_bakytbek
Valued Contributor III

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

MVP
MVP

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
beck_bakytbek
Valued Contributor III

Re: Nullvalue in Aggr

Hi Jonathan,

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

Community Browser