Dynamic max and min value based on another dimension in set analysis

[chaorenzhu]

Hi experts,

For simplicity, I have a simple table as below.

year	type	value
2018	A	10
2019	A	12
2020	A	15
2019	B	6
2020	B	7
2019	C	5

I want to calculate the difference of sum(value) between the max year and min year avaiable for each type.

I have created 2 variables, vMaxYear=max(year) and vMinYear=min(year), and use below expression for calculation:

sum({<year={"$(vMaxYear)"}>}value)-sum({<year={"$(vMinYear)"}>}value)

For type A it gives the correct value as 15-10=5. However, for type B, I want it to return 7-6=1 but because there is no value for 2018, the expression returns 7-0=7.

And for type C, I want it to return 5-5=0, but as there is no value for both 2018 and 2020, it returns null.

Need your advice on how I can achieve the intended result. Thank you!

[Taoufiq_Zarra]

@chaorenzhu  maye be like :

=FirstSortedValue(value,-year)-FirstSortedValue(value,year)

output:

[chaorenzhu]

Hi Taoufiq,

Thanks for the idea. I thought about using firstsortedvalue but the thing is, in my real data, for one type and one year, there could be thousands of rows and I'd like to get sum(value). I also don't want to do the aggregation during script loading as I'd like to preserve the raw data so that I could apply filter in other dimensions.

Any idea how to achieve that? Thanks.