Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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
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.
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]))
Great.
The solution is correct for my exemple, I'll aplicate it in my business.
Thank you.