Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I have a peculiar problem regarding the combination of a formula that is supposed to show an aggregated median of a ratio within a statistical year.
The following formulas do work for the year that has been selected:
Median(aggr({$<StatYear={$(=max(StatYear))}>}sum(MeasureA/MeasureB), AggregationDimension)
Median(aggr(sum({$<StatYear={$(=max(StatYear))}>}MeasureA/MeasureB), AggregationDimension)
However, I want to display previous year data as a comparison, as well. So I tried the following:
Median(aggr({$<StatYear={$(=max(StatYear)-1)}>}sum(MeasureA/MeasureB), AggregationDimension)
Median(aggr(sum({$<StatYear={$(=max(StatYear)-1)}>}MeasureA/MeasureB), AggregationDimension)
But I don't receive any output, unless I select both years. I do not want to do this, though, because it would mess up the rest of my report.
Also, this applies only tor this specific formula. When simply calculating the mean or the median without any aggregation*, my report will display previous year data without any problems, even though only one year is selected.
*sum({$<StatYear={$(=max(StatYear)-1)}>}MeasureA/MeasureB)
*median({$<StatYear={$(=max(StatYear)-1)}>}MeasureA/MeasureB)
Where am I messing up?
Have you considered deriving the previous year as a variable and using that instead? Also, you might want to include the year field as part of the Aggregation Dimensions.
Hi Peter,
thanks for your reply and your ideas.
I wouldn't want to use variables, as I would want to display values for both years in one chart at the same time so I can compare them. Also, I would have to restructure my whole report due to this one formula.
Combining the previous year with the aggregation dimension won't do the trick.
Also, I made sure before, that the aggregation dimension will never have the same value for two different years.