First of all thanks in advance for helping! This community has been a huge help for me so far without even needing to post something. The time has finally arrived that I have to post something as I have not been able to find the right answer. The following has been keeping me busy for the last 2 days.
A customer wants to see the day-to-day stock information of their products per silo. Meaning that they select a date and they want to see everything that has been added to or used from that silo, previous to that date.
Their system creates a row in the database each time the silo is completely empty, meaning that I only have to calculate the stock from the selected date back in time until the first “Empty” message. Everything before this message is irrelevant.
I’ve tried coming up with a decent illustration of what I mean. See below:
Date
Time
Weight(kg)
Silo
Code
25/02/2016
04:00
+2000
6
008
25/02/2016
03:45
+1000
6
007
24/02/2016
23:01
+1000
6
006
24/02/2016
14:51
0
6
EMPTY
24/02/2016
14:50
-6700
6
005
24/02/2016
14:42
+2300
6
004
24/02/2016
14:32
+3400
6
003
24/02/2016
13:58
-1400
6
002
24/02/2016
13:42
+2400
6
001
Let´s say they have selected the 25th of February and silo 6. They now need only need to see the rows with code 006, 007 and 008.
My set analysis looks like this: SUM({<Date = {"<=$(=Max(Date))"}>}Weight)
But this also shows all the rows before the empty message.
I’ve also tried: =SUM({<Datum = {">=$(=Min({<Code = {'EMPTY'}>} Date)) <=$(=Max(Date))"}>}Weight) to get the minimum date with the code EMPTY but there can be more than 1 when going back in time.
Do you guys have any idea how to find the first EMPTY value prior to the selected date?
