16 Replies Latest reply: Feb 10, 2018 8:28 PM by Sunny Talwar

# Accumulating count with 2 dimensions

I have an expression to accumulate a count using RangeSum and Above. The issue is that when I add a dimension it no longer performs the RangeSum and Above. I can see why it does not work as when i create a pivot table using the same logic, it does not accumulate the value as it now needs to sum from the right not above. I have trolled through the community but no one seems to have something similar. What am i missing?

• ###### Re: Accumulating count with 2 dimensions

Usually you can use aggr to get both dimensions

try like this:

sum( Aggr (

RangeSum(Above(TOTAL Count({<Date = {'\$(=Date(vDayCurrent))'}, System = {'Source'}>} feedId),0,RowNo(TOTAL))),

Dimension2,Dimension1))

• ###### Re: Accumulating count with 2 dimensions

Tried that the other day and that does not work.

• ###### Re: Accumulating count with 2 dimensions

Which way are you trying to accumulate? Going down or going across? For going across you need to use Before()/After() functions instead of Above()/Below() functions and use ColumnNo() instead of RowNo() function

• ###### Re: Accumulating count with 2 dimensions

So I can now get the Pivot table working correctly but when I use the same logic in the line chart I get nothing. What frustrates me with this program is if you can do something in a pivot table why cant one use the same logic for a chart? Too often, I need to adapt the expression again. What is the logic behind this?

• ###### Re: Accumulating count with 2 dimensions

You might see it as restrictive and frustrating, but I see it as making it more intuitive for developers. If you look at this in a pivot table, If you want to accumulate horizontally, would you say that you would want to go above or below? or would saying After and Before would make more sense? Similarly, it is the same logic for ColumnNo() vs RowNo().

To differentiate, pivot table is an exception where After/Before and ColumnNo, else all other objects will need Above/Below and RowNo(). If you really hate After/Before and would like to use Above/Below for even a pivoted dimension of a pivot table, you can use it with Aggr(). Because when you use Aggr(), the imaginary table which it creates is similar to a straight table, the Before/After function stops working. So, now you can use Above/Below.

Missing Manual - Before() and After()

• ###### Re: Accumulating count with 2 dimensions

Hi, I really appreciate this as coming from an excel background is different to a developer. However, how do I get my line chart to function correctly though? I used the above and it does not show the expected result like the pivot table.

• ###### Re: Accumulating count with 2 dimensions

This is the code that works for the pivot table but I cannot get it to work for the line chart.

RangeSum(Before(TOTAL Count({&lt;Date = {'\$(=Date(vDayCurrent))'}, System = {'Source'}>} feedId),0,ColumnNo(TOTAL)))

I have put aggr around it and taken out TOTAL and nothing seems to work. I dont get the logic differences given it works in a pivot table but not for the line chart.

• ###### Re: Accumulating count with 2 dimensions

Would you be able to share a sample? Might help to see what you have to give a better response

• ###### Re: Accumulating count with 2 dimensions

Here you go

• ###### Re: Accumulating count with 2 dimensions

Try this

Aggr(RangeSum(Above(Count({<Date = {'\$(=Date(vDayCurrent))'}, System = {'Source'}>} feedId), 0, RowNo())), Portfolio2, (Interval, (NUMERIC)))

• ###### Re: Accumulating count with 2 dimensions

Excellent again, I would never have got the NUMERIC. I cant even find it in the Qlik help. Cheers

• ###### Re: Accumulating count with 2 dimensions

This was a new function which was introduced in Qlik Sense and QV12 or above... you can read about it here

The sortable Aggr function is finally here!

• ###### Re: Accumulating count with 2 dimensions

I have found a minor issue with this, when I try to ensure that it is not affected be selections by inserting in {1}, it does not work. I tried it in all sorts of spots but to no avail.

If(Weekday(\$(vDayCurrent))=2,

Aggr({1}RangeSum(Above(Count({<Date = {'\$(=Date(vDayFriday))'}, System = {'Source'}>} feedId), 0, RowNo())), Portfolio2, (Interval, (NUMERIC))),

Aggr({1}RangeSum(Above(Count({<Date = {'\$(=Date(vDayCurrent))'}, System = {'Source'}>} feedId), 0, RowNo())), Portfolio2, (Interval, (NUMERIC))))

• ###### Re: Accumulating count with 2 dimensions

Try this

If(Weekday(\$(vDayCurrent))=2,

Only({1} Aggr(RangeSum(Above(Count({1<Date = {'\$(=Date(vDayFriday))'}, System = {'Source'}>} feedId), 0, RowNo())), Portfolio2, (Interval, (NUMERIC)))),

Only({1} Aggr(RangeSum(Above(Count({1<Date = {'\$(=Date(vDayCurrent))'}, System = {'Source'}>} feedId), 0, RowNo())), Portfolio2, (Interval, (NUMERIC)))))

• ###### Re: Accumulating count with 2 dimensions

Thanks guru, not sure would have figured that out at all. What type of function is Only?