Set Analysis to sum amount based on max version

[spinchuk]

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!

[#2]

well,

1. 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'...

[spinchuk]

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?

[maxgro · Accepted solution]

try with

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

[spinchuk]

Massimo - you're the best.

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

[Anonymous]

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,