Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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?
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
it´s close, but there can be the same calcversion several times. and then i need to sum all the costs. see attached file...
found the solution
aggr(Sum ({<CalcVersion = {$(=Max(CalcVersion))}, Month=,Year=>}Costs), Product)
Thanks anyway!!
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)
yeah I just noticed my idea doesn´t work perfect. need to check
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.
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?
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...
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?