5 Replies Latest reply: Jan 8, 2011 4:51 PM by Patrick MAZZUCCHI

# Display the value associated minimum or maximum date to calculate a consumption between two dates.

Good day Qlikviewers !

I tried to make a calculation of consumption, with stock levels on data.

 Business unit Stock date Stock A FLORIDA 01/10/2010 3690 FLORIDA 02/10/2010 3642 FLORIDA 27/10/2010 6075 FLORIDA 28/10/2010 5942 FLORIDA 30/10/2010 5656 FLORIDA 31/10/2010 5518 FLORIDA 01/11/2010 5375 FLORIDA 02/11/2010 5239 FLORIDA 29/11/2010 5313 FLORIDA 30/11/2010 5224 GEORGIA 10/10/2010 5787 GEORGIA 11/10/2010 5751 GEORGIA 11/10/2010 5770 GEORGIA 29/10/2010 4353 GEORGIA 30/10/2010 4222 GEORGIA 31/10/2010 4115 GEORGIA 01/11/2010 4039 GEORGIA 02/11/2010 3979 GEORGIA 02/11/2010 3997 GEORGIA 29/11/2010 5675

I just want to calculate the difference to make a consumption. (No suppling in this selection, so negative consumtions)

My results expected:

 (NO FILTERS) Begin STOCK A End STOCK A CONSUMPTION FLORIDA 3690 5224 -1534 GEORGIA 5787 5675 112 STOCK DATE=OCTOBER Begin STOCK A End STOCK A CONSUMPTION FLORIDA 3690 5518 -1828 GEORGIA 5787 4115 1672

I realy don't manage to make the first step : to choose the value for the minimum and maximum dates !

I am sure it is not complicated, but I tried many solutions with aggregations and set analysis, (I have read many posts on the forum), and I don't know what is the better calculation.

Best Regards.

• ###### Display the value associated minimum or maximum date to calculate a consumption between two dates.

Hi, with this expression are you able to get the initial stock?

`Sum (if([Stock date] = aggr(Min([Stock date]),[Business unit]) ,[Stock A]))`

but with S.A will be better i guess

rgds

• ###### Re. :Display the value associated minimum or maximum date to calculate a consumption between two dates.
Normal 0 21 false false false FR X-NONE X-NONE

Hello Patrick,

Here is for the version with Set Analysis :

=Sum ({\$<[Stock date] = {'=Aggr(Min([Stock date]),[Business unit])'} >} [Stock A])

Bye

Have a Good Week

• ###### Display the value associated minimum or maximum date to calculate a consumption between two dates.

Both solutions are working, thank you.

But I try to do the same with the maximum.

Attached file updated.

Sum ({\$<[Stock date] = {'=Aggr(Max([Stock date]),[Business unit])'} >} [Stock A]) = result = the minimum !

OR

Sum

(if([Stock date] = aggr(Max([Stock date]),[Business unit]) ,[Stock A])) = result = 0

Can you help me (again)

Cordialement.

• ###### Re. :Re: Display the value associated minimum or maximum date to calculate a consumption between two dates.

Hello,

It's strange the Max don't work with the SA.

Here the solution without Set Analisys but working with max :

Normal 0 21 false false false FR X-NONE X-NONE

Min : sum(aggr(if(Date([Stock date]) = Date(Min(TOTAL <[Business unit]> [Stock date])),Min([Stock A])),[Business unit], [Stock date]))

Max : Normal 0 21 false false false FR X-NONE X-NONE sum(aggr(if(Date([Stock date]) = Date(Max(TOTAL <[Business unit]> [Stock date])),Min([Stock A])),[Business unit], [Stock date]))

• ###### Re. :Re: Display the value associated minimum or maximum date to calculate a consumption between two dates.

Great.

The solution is correct for my exemple, I'll aplicate it in my business.

Thank you.