Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a scenario where in i need to choose the minimum date of the dimension value in straight table data irrespective of selection date range. Using this date i need to calculate amount.
AGGR(Min({$<Date=,>} Date),PrdCtg) - This is returning minimum date for the PrdCtg
Sum({$<Date={"$(=date(Min(Date)))"},>} Amount) for Amount column. With this expression it is considering the minimum from the selected date range, which is not expected. I am expecting it to pick the date returned by the Aggr function.
How can we achieve this ?
Any suggestions are much appreciated.
This is one of the such cases where set analysis can't help. Even if you put aggr() outside the set analysis expression, it is still going to avoid the dimension context of aggr(). It's like - set analysis evaluates once for an entire object (think of a chart with dimension). Hence, your min(Date) - would always give one minimum value against the entire data set the scope and not per dimension value that you pass in the aggr() or in the chart dimension.
So how to tackle such cases ? - avoid set analysis and use Sum(Aggr(if... kind of structure. Or, create additional dimension in the script (usually combination of two or more fields)...and then you could probably be able to use set analysis. For your case, try like:
Sum(If(Aggr(NODISTINCT Min(Date),PrdCtg)=Date,Sales))