Using aggr in set analysis

elijahabel — Wed, 22 Dec 2021 15:42:54 GMT

I have this problem where I need to be able to use a hierarchy for the visual, and count only the days which have an overall profit. This is in a flat table.

For example, if my hierarchy is REGION=>DIVISION, and I have a region selected, next to each division it should show me the number of days where the sum of the revenue is greater than zero.

I have an expression, but it is only giving me the number of days greater than zero at the level of hierarchy above what is being displayed:

COUNT({<DATE={"=Sum([REVENUE])>0"}>} DISTINCT DATE)

All divisions will show as having the same number of Days Up, but after inspection it is clear that this number is coming from the Days Up by Region.

Hierarchy (DIVISION)	Days up
Division A	3
Division B	3
Division C	3

I have a variable vLevelBelow which properly stores the level of the hierarchy we are currently at (in this case Division), but if I try to aggregate on this within the set analysis, I get all zeroes:

COUNT({<DATE={"=aggr(Sum([REVENUE]),$(=$(vLevelBelow)),DATE)>0"}>} DISTINCT DATE)

Any ideas? Thanks for all help.

Re: Using aggr in set analysis

andrey_krylov — Fri, 26 Oct 2018 05:12:26 GMT

Hi, Elijah. I think set analysis is not appropriate for this issue, since it restricts all data (not every row separately). Try this expression

Sum( If(Aggr(Sum(REVENUE), DATE, DIVISION) > 0, 1 , 0)

Re: Using aggr in set analysis

elijahabel — Fri, 26 Oct 2018 19:23:58 GMT

This was perfect, thank you very much Andrey!

topic Re: Using aggr in set analysis in App Development

Using aggr in set analysis

Re: Using aggr in set analysis

Re: Using aggr in set analysis