Max problem

[michael_maeuser]

Hi,

I have a problem with my precalculation. There are many different versions. The version with the highest number is always the newest one. So I want a table that standardly shows the newest version. But if I select another version it should change. Then I put max in set analysis. But this way it ignores the product no. Attached is sample data. Thanks for any help!

[swuehl]

Michael,

try

=FirstSortedValue(Costs, -CalcVersion)

as expression in your table chart.

If you need to sum Costs, you could also use:

=FirstSortedValue(aggr(sum(Costs),Product,CalcVersion),-CalcVersion)

Hope this helps,

Stefan

[michael_maeuser]

it´s close, but there can be the same calcversion several times. and then i need to sum all the costs. see attached file...

[michael_maeuser]

found the solution

aggr(Sum ({<CalcVersion = {$(=Max(CalcVersion))}, Month=,Year=>}Costs), Product)

Thanks anyway!!

[swuehl]

This will only show a value for the product with the total maximum CalcVersion. Is this what you want?

The set analysis is calculated once per chart, not per dimension value.

I haven't found anything different in your last sample file compared to your first one, so I assume you have unintentionally attached again the first.

You could also try something like

=aggr(if(CalcVersion=max(total<Product> CalcVersion),sum(Costs)),Product,CalcVersion)

[michael_maeuser]

yeah I just noticed my idea doesn´t work perfect. need to check

[michael_maeuser]

your last expression is basically the solution. in my real data i have 1 field from an inline load (values: actual and Budget) i use this field as a second dimension and put an if statement in my expression if(Dim=Actual, sum(actcosts), sum(budget)... in combination with that field i just don´t get it work. do you also have an idea for that? thanks so much for your help.

[swuehl]

So your Dim is based on a data island?

Try maybe like

if(Dim=Actual

,aggr(if(CalcVersion=max(total<Product> CalcVersion),sum(actcosts)),Product,CalcVersion)

,aggr(if(CalcVersion=max(total<Product> CalcVersion),sum(budget)),Product,CalcVersion)

)

Or am I missing something completely?

[michael_maeuser]

yes it´s an island. i already had exactly the expression you suggested. but somehow it seems like that qlikview automatically thinks it´s actual. if i create an table with dimension product and the artificial field plus the expression =aggr(if(CalcVersion=max(total<Product> CalcVersion),sum(Costs)),Product,CalcVersion), then i only see actual. i thought i should see the same values for actual and budget since it is an data island...

[swuehl · Accepted solution]

Well, I just noticed it should probably be

if(Dim = 'Actual'

i.e. using single quotes.

And you could / should try using a NODISTINCT qualifier to see the duplicate value in your table for both DIM values (since aggr() uses an implicite DISTINCT qualifier):

=aggr( NODISTINCT if(CalcVersion=max(total<Product> CalcVersion),sum(Costs)),Product,CalcVersion)

I haven't fully understood your issue, could you post a small sample app?