Accumulating count with 2 dimensions

[Anonymous]

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?

[sunny_talwar · Accepted solution]

Try this

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

[Anonymous]

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

[sunny_talwar]

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!

[Anonymous]

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

[sunny_talwar]

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

[Anonymous]

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

[sunny_talwar]

Check here

How to use - Only()