topic Re: Sum of aggr function in QlikView

Sum of aggr function

patrickbender — Sun, 28 Oct 2018 14:50:11 GMT

Hi,

I have a QV table with an expression that looks like below.

As you can see from the picture it doesn't seem to add upp in the column total and I can't figure out why.

The expression looks like this:

MålkortsDatum = Date formated as YYYY-MM-DD

SVDate (Variable) = 2018-10-30

SlutprognosAnsvar = number column (data from excelfile)

ProjektNr = number column (data from excelfile)

AnsvarNr = number column (data from excelfile)

FirstSortedValue({$<MålkortsDatum={"<$(SVDate)"}>} distinct aggr(Sum({1<MålkortsDatum={"<$(SVDate)"}>}SlutprognosAnsvar), ProjektNr, AnsvarNr, MålkortsDatum),-MålkortsDatum)

Re: Sum of aggr function

swuehl — Sun, 28 Oct 2018 15:00:45 GMT

If you say 'add up' that means you want a sum of rows in your pivot table?

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/ChartFunctions/NestedAggregations/sum-of-row…

Try adding the the Sum(Aggr(..., YourChartDimensions)) like

=Sum({$<MålkortsDatum={"<$(SVDate)"}>} Aggr(

FirstSortedValue({$<MålkortsDatum={"<$(SVDate)"}>} distinct aggr(Sum({1<MålkortsDatum={"<$(SVDate)"}>}SlutprognosAnsvar), ProjektNr, AnsvarNr, MålkortsDatum),-MålkortsDatum)

,ProjektNr, AnsvarNr))

Re: Sum of aggr function

patrickbender — Sun, 28 Oct 2018 15:05:13 GMT

That is awesome!
Why does it work when using a second aggr argument?

Re: Sum of aggr function

swuehl — Sun, 28 Oct 2018 15:15:24 GMT

Totals in Charts

Re: Sum of aggr function

patrickbender — Mon, 29 Oct 2018 10:55:36 GMT

Thank you for the information Stefan,
I ended up rewriting the expression after getting a better understanding of the expressions and this is the end result:

Sum({$<MålkortsDatum={"<$(SVDate)"}>}AGGR(FirstSortedValue(SlutprognosAnsvar, -MålkortsDatum), ProjektNr, AnsvarNr))