topic Re: Calculated dimension aggregation issue in QlikView

Calculated dimension aggregation issue

Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Calculated dimension aggregation issue

sunny_talwar — Wed, 19 Oct 2016 10:37:54 GMT

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])

Re: Calculated dimension aggregation issue

sunny_talwar — Wed, 19 Oct 2016 10:38:39 GMT

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

Re: Calculated dimension aggregation issue

tresB — Wed, 19 Oct 2016 11:02:38 GMT

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])

Re: Calculated dimension aggregation issue

Wed, 19 Oct 2016 12:38:13 GMT

Thank you both! Works as expected