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

[#1]

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.

Thanks in advance for your help.

Best Regards.

[hector · Accepted solution]

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

[#3]

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

[#4]

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.

[#5]

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]))

[#6]

Great.

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

Thank you.