Using aggr in set analysis

[elijahabel]

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.

[andrey_krylov · Accepted solution]

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)

[elijahabel]

This was perfect, thank you very much Andrey!