topic Re: How to get total value based on max year for each dimension in QlikView

How to get total value based on max year for each dimension

jagannalla — Mon, 12 Jan 2015 07:24:27 GMT

Hi,

I've data like this

LOAD * INLINE [

Prod, Val, Year

A, 10, 2014

A, 20, 2014

B, 30, 2014

B, 40, 2015

];

Now in st.table I'll take Prod as dimension, and I need to expression to get the values,20,40 for A,B. For A the max year is 2014 and the value is 20, for B the max year is 2015 and the value is 40.

How can I achieve this ?

Thanks,

Jagan

Re: How to get total value based on max year for each dimension

anbu1984 — Mon, 12 Jan 2015 07:30:34 GMT

Expr: FirstSortedValue(Val,-(Year+Val))

Re: How to get total value based on max year for each dimension

jagannalla — Mon, 12 Jan 2015 07:39:51 GMT

I tried this but, I want to aggregate the value. B'coz I've lot values there

Re: How to get total value based on max year for each dimension

anbu1984 — Mon, 12 Jan 2015 07:47:33 GMT

So far Prod A, expected output is 30(10+20)?

Re: How to get total value based on max year for each dimension

jagannalla — Mon, 12 Jan 2015 07:49:41 GMT

Exactly sorry for the mistake.

Re: How to get total value based on max year for each dimension

anbu1984 — Mon, 12 Jan 2015 08:08:10 GMT

check this

Re: How to get total value based on max year for each dimension

anbu1984 — Mon, 12 Jan 2015 09:10:39 GMT

Dim: Prod

Expr: SubField(Concat(Aggr(Sum(Val),Prod,Year),',',-Year),',',1)