Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I'm trying to make a measure with set analysis that makes a calculation based on input of the dimension. It works when I select one of the items in the dimension but it stops when there is no selection. When there is no selection I would like the expression to calculate it for all items in the dimension
group | start | end
group A | Jul-2019 | Jul-2020
group B | Sep-2019 | Mar-2020
I want to sum the sales with the start and end data of the groups. First I created a big table and made a pick&match formula to loop through the groups. This works but not if the groups dimension is not in there. So I could not use it in a KPI.
I created the following, which works, but only if I select a group. When I don't have a selection I get zero
Sum({<Date={">$(=num(Max(Aggr(Date#(start,'YYYY-MM'),group))))<=$(=num(Max(Aggr(Date#(stop,'YYYY-MM'),group))))"}>} Sales)
I also tried with Aggr before the Sum function but the results where the same. If Group A has 2,000$ in their period and Group B has 3,000$ in their period I would like to have a KPI giving me 5,000$
Try this
If(GetSelectedCount(group)>0, Sum(Saels), Sum({<Date={">$(=num(Max(Aggr(Date#(start,'YYYY-MM'),group))))<=$(=num(Max(Aggr(Date#(stop,'YYYY-MM'),group))))"}>} Sales))
Try this
If(GetSelectedCount(group)>0, Sum(Saels), Sum({<Date={">$(=num(Max(Aggr(Date#(start,'YYYY-MM'),group))))<=$(=num(Max(Aggr(Date#(stop,'YYYY-MM'),group))))"}>} Sales))