3 Replies Latest reply: Dec 29, 2011 1:04 PM by Stefan Wühl RSS

    Firstsortedvalue and stock levels



      I have a table with stock levels like:


      ID,      Date,      Quantity

      1, '01/12/2011', 11

      1, '04/12/2011', 22

      1, '08/12/2011', 55

      1, '10/12/2011', 33


      and a calendar table associated by Date field.


      I would like to display the stock levels for every possible day in a given date range. I was trying to use the FirstSortedValue() function, but without any luck so far. I'm stuck without any clue how to solve this.

      I would like to avoid filling the missing data in the load script.


      Anyone can help?


      Thanks, Przemek

        • Firstsortedvalue and stock levels
          Stefan Wühl

          When you are saying 'for every possible day in a given date range', how do you create the date range? Do you work with a master calendar?


          One way to solve it would be to create FromDate and ToDate in the script giving the date range that the quantity is valid, and then use IntervalMatch together with a master calendar.


          If you don't want to use a master calendar (which I would suggest to use anyway), could you describe how you want to display / create the date range?

            • Firstsortedvalue and stock levels

              I do have a master calendar.

              I was thinking about using IntervalMatch, but that would give me a lot of data redundancy and would probably influence performance.


              I could use seperate calendar to create the date range. I am not sure if that's the right approach and how should the expressions look like in such case.


              Many thanks, Przemek

                • Firstsortedvalue and stock levels
                  Stefan Wühl



                  if you want to keep the calendar and the Quantity tables separated you could use something like this:


                  LET vDateMin = Num(MakeDate(2011,1,1)); 

                  LET vDateMax = Floor(MonthEnd(Today())); 

                  LET vDateToday = Num(Today()); 





                  Date($(vDateMin) + RowNo() - 1) AS CalendarDate 

                  AUTOGENERATE 1 

                  WHILE $(vDateMin)+IterNo()-1<= $(vDateMax); 



                  LOAD * INLINE [

                  ID,      Date,      Quantity

                  1, '01/12/2011', 11

                  1, '04/12/2011', 22

                  1, '08/12/2011', 55

                  1, '10/12/2011', 33



                  LOAD ID, Date as DateFrom, alt(previous(Date)-1,$(vDateMax)) as DateTo, Quantity resident Data order by Date desc;

                  drop table Data;


                  And then a simple chart with dimension CalendarDate and as expression:

                  =sum(if(CalendarDate<=DateTo and CalendarDate >= DateFrom, Quantity))


                  This will probably be not very performant and I would personally go for doing it in the script.

                  You could do it with an intervalmatch or by filling in all missing values in your Quantity table.


                  Something similar is also just being discussed here: