Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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 unitStock dateStock A
FLORIDA01/10/20103690
FLORIDA02/10/20103642
FLORIDA27/10/20106075
FLORIDA28/10/20105942
FLORIDA30/10/20105656
FLORIDA31/10/20105518
FLORIDA01/11/20105375
FLORIDA02/11/20105239
FLORIDA29/11/20105313
FLORIDA30/11/20105224
GEORGIA10/10/20105787
GEORGIA11/10/20105751
GEORGIA11/10/20105770
GEORGIA29/10/20104353
GEORGIA30/10/20104222
GEORGIA31/10/20104115
GEORGIA01/11/20104039
GEORGIA02/11/20103979
GEORGIA02/11/20103997
GEORGIA29/11/20105675


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 AEnd STOCK ACONSUMPTION
FLORIDA36905224-1534
GEORGIA57875675112
STOCK DATE=OCTOBERBegin STOCK AEnd STOCK ACONSUMPTION
FLORIDA36905518-1828
GEORGIA578741151672


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.

1 Solution

Accepted Solutions
hector
Specialist
Specialist

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

View solution in original post

5 Replies
hector
Specialist
Specialist

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

Not applicable
Author

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

Not applicable
Author

Both solutions are working, thank you. Smile

But I try to do the same with the maximum. Hmm

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.



Not applicable
Author

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


Not applicable
Author

Great.

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

Thank you.