topic Re: Max problem in QlikView

Max problem

michael_maeuser — Mon, 11 Jun 2012 07:13:59 GMT

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!

Max problem

swuehl — Mon, 11 Jun 2012 07:46:14 GMT

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

Re: Max problem

michael_maeuser — Mon, 11 Jun 2012 09:03:36 GMT

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

Re: Max problem

michael_maeuser — Mon, 11 Jun 2012 09:14:26 GMT

found the solution

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

Thanks anyway!!

Re: Max problem

swuehl — Mon, 11 Jun 2012 09:18:10 GMT

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)

Re: Max problem

michael_maeuser — Mon, 11 Jun 2012 09:19:48 GMT

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

Re: Max problem

michael_maeuser — Mon, 11 Jun 2012 09:47:40 GMT

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.

Re: Max problem

swuehl — Mon, 11 Jun 2012 09:51:52 GMT

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?

Re: Max problem

michael_maeuser — Mon, 11 Jun 2012 10:01:07 GMT

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

Re: Max problem

swuehl — Mon, 11 Jun 2012 10:08:05 GMT

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?

Re: Max problem

michael_maeuser — Mon, 11 Jun 2012 11:00:32 GMT

that was the solution! perfect!