topic Set Analysis and Aggregation in App Development

Set Analysis and Aggregation

hppflpp — Fri, 19 Aug 2022 08:15:43 GMT

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?

Re: Set Analysis and Aggregation

BrunPierre — Sun, 21 Aug 2022 15:17:57 GMT

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.

Re: Set Analysis and Aggregation

hppflpp — Mon, 22 Aug 2022 13:35:53 GMT

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.