Skip to main content
Announcements
Join us on Feb.12 to Discover what’s possible with embedded analytics: REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
hppflpp
Contributor II
Contributor II

Set Analysis and Aggregation

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? 

Labels (1)
2 Replies
BrunPierre
Partner - Master
Partner - Master

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.

 

 

hppflpp
Contributor II
Contributor II
Author

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.