Calculated dimension aggregation issue

[#1]

Hello,

I am overthinking this one for sure. And I need your help

I load this data:

LOAD * INLINE [
    Project, Indicator Name, Indicator temp Value, Indicator Final, Week
    Proj, One, a, b, W35
    Proj, One, b, b, W36
    Proj, Two, a, a, W35
    Proj, Two, a, a, W36
];

And creating a pivot table (with Expression: =concat([Indicator Final]) )

Instead of Indicator temp Value I am unable to come up with a calculated dimension which would show me Indicator temp value for latest week only, like that:

Project	Indicator Name	Indicator temp Value as per latest week	Week	W35	W36
Proj	One	b		b	b
Proj	Two	a		a	a

What should I use?

Thanks!

[sunny_talwar · Accepted solution]

I have added a WeekNum field

LOAD * INLINE [ 

    Project, Indicator Name, Indicator temp Value, Indicator Final, Week  , WeekNum

    Proj, One, a, b, W35  , 35

    Proj, One, b, b, W36  , 36

    Proj, Two, a, a, W35  , 35

    Proj, Two, a, a, W36  , 36

]; 

And used a calculated dimension for Indicator Last Temp Value

=Aggr(FirstSortedValue([Indicator temp Value], -WeekNum), Project, [Indicator Name])

[sunny_talwar]

Note that WeekNumber may not be enough if you have more than 1 year of data and you should create a numerical WeekYear and use that in the firstSortedValue function

[tresesco]

And,

if you don't want to/can't modify script, you can try the expression like:

=Aggr(FirstSortedValue([Indicator temp Value], -PurgeChar(Week,'Ww')), Project, [Indicator Name])

[#5]

Thank you both! Works as expected