3 Replies Latest reply: Jun 3, 2013 4:28 AM by Gysbert Wassenaar RSS

    Get minimum date of a set

    Kaushik Solanki
      Dear Exports,
           I need help regarding below scenario. Hope someone will have solution for the same.
           Problem Scenario:
           I have a variable which holds a value say 2000.
           I have a table which has the transactions by date, something like shown below.
          
      Transaction Date
      Quantity
      1/1/2013
      1000
      1/10/2013500
      2/5/2013
      1200
      2/20/2013700
      3/13/2013300
      3/18/2013
      900
      3/25/2013500
      4/3/2013200
      4/16/2013500
      4/24/2013800
      4/30/2013500
      The Date format is MM/DD/YYYY.
      What i want is if I pass the Date say 1st Mar 2013 then it should add the quantity from 1st Mar 2013 and going in descending order and check for the sum of quantity and stop on the date where the sum of quantity from 1st Mar 2013 to that day is lesser than the variable quantity.
      So the out put should be 2/5/2013 date.
      I am here by sending the excel for reference.
      Thanks in Advance.
        • Re: Get minimum date of a set
          Gysbert Wassenaar

          I hope I understood your requirements correctly. See attached qvw.

            • Re: Get minimum date of a set
              Kaushik Solanki

              Thanks Gysbert,

               

                   The logic is really good, but the thing is i have more dimensions apart from just date, so if I merge AsofDate with each parameter, the table will be too huge.

               

                   The count for the dimensions are

               

                   Item      -  1 million.

                   Months -  48

                   Salesperson - 200

                   Divisions  - 20

               

                   I hope you understand what I want to say.

               

                   Do you think creating a table will be better then using the logic suggested by you in script?

               

                   Thanks again for a wonderful logic.

               

              Regards,

              Kaushik Solanki    

                • Re: Get minimum date of a set
                  Gysbert Wassenaar

                  I don't think you need to merge AsofDate with each parameter, only with the date field. In the example I joined the AsofDate with the main table, but that is not really necessary. You can simply keep it as a separate table linked to the data field.

                   

                  If you have 48 months then I assume that means about two years. That would mean about 730 unique date values. The AsofDate table would at contain at most (730^2+730)/2 = 266815 records. That's not all that large. And it will compress quite nicely since there are only 730 unique values.

                   

                  If you add the dimensions to your chart then you should get the correct results from the expression for the dimension combinations.