topic Re: Set Analysis to sum amount based on max version in QlikView

Set Analysis to sum amount based on max version

spinchuk — Fri, 25 Jul 2014 17:05:10 GMT

Guys,

Can any body help with creating expression to calculate Amount for Max(Version) or row in data?

My data is like table on the left, and I need results in table on the right (see pic below):

Need the expression for "Latest" column. Currently, Estimate B missing values.

Also, I attached QVW with instructions.

Thank you!

Re: Set Analysis to sum amount based on max version

Fri, 25 Jul 2014 19:21:46 GMT

well,

Create two variables:

vMaxA =Max( {< Estimate = {'A'} >} Version)

vMaxB =Max( {< Estimate = {'B'} >} Version)

2. In the chart expression:

If( Estimate = 'A', Sum({$<

Version = {$(vMaxA)} >}

Amount),

Sum({$<

Version = {$(vMaxB)} >}

Amount))

I think is a ugly response. It could be better using 'aggr'...

Re: Set Analysis to sum amount based on max version

spinchuk — Fri, 25 Jul 2014 19:27:33 GMT

Thanks Bernardo, but A and B is just an example of data .. i have thousands of different entries for dimension , so creating variables for each value in dimension will not work.

Any other ideas please?

Re: Set Analysis to sum amount based on max version

maxgro — Fri, 25 Jul 2014 19:44:57 GMT

try with

sum(aggr(FirstSortedValue(Amount, -Version),Estimate,Category) )

Re: Set Analysis to sum amount based on max version

spinchuk — Fri, 25 Jul 2014 19:52:04 GMT

Massimo - you're the best.

Works like a charm - exactly what I was looking for.

Re: Set Analysis to sum amount based on max version

Anonymous — Fri, 25 Jul 2014 21:48:35 GMT

Hi spinchuk,

I hope this can help to solve your issue:

1. Dimensions Tab -> click on Add Calculated Dimension and add the following expresion

2. Select the calculated dimension and check Enable Conditional box. Then type 0

3. In Expresions tab, select Latest (no total) expresion and the following formula

This will generate the following table:

Regards,