4 Replies Latest reply: Jan 17, 2012 2:39 PM by Federico Levi RSS

    Finding Date when reaches target in straight table

      Hello!

      I have 2 tables.

       

      Lets say table1 is:

      TargetUnits

      TargetDate

      Product

       

      Table2 is:

      Date

      Units

      Product

       

       

      And I need to show

      Product

      TargetDate

      TargetUnits

      Date reaching target

       

       

      I cant find the way to acumulate sum until reaching the target by product and obtain the corresponding date.

       

       

      Thanks in advance!

        • Re: Finding Date when reaches target in straight table
          Karl Pover

          You should able to do something like the following formula to calculate the average number of units sold per day and divide that by the target units to get the number of days needed to reach the target units.  You would then add that number of days to starting date.

           

          min(Date) + sum(TargetUnits) / (sum(Units)/(max(Date)-min(Date)))

           

          Karl

           

          Edit:  Oops, I thought the column was the date when the target is to be reached and not the date the target was reached.

            • Finding Date when reaches target in straight table

              I think the problem there is that the date may not be accurate because the transactions aren't linear.

              Day 1 can move 30 units and day 2 500 for instance.

                • Re: Finding Date when reaches target in straight table
                  Stefan Wühl

                  Maybe like attached?

                   

                  This uses an accumulation in the script and a flag to indicate when the threshold is reached.

                   

                  I think it could get quite hard to do this in the front end only. It looks like a enforced version of a pareto chart (how many days needed to get a threshold (per product) vs. how may customers make up some share of revenue). So I was hoping to get some support by the new dimension limits in QV11, but have not succeeded yet.

                   

                  Regards,

                  Stefan

                   

                  P.S. If you can't open my attachment:

                   

                  Script to create sample data:

                   

                  Target:

                  LOAD * INLINE [

                  TargetUnits, TargetDate, Product

                  100, 17.01.2012, P1

                  120, 18.01.2012, P2

                  ];

                   

                  Temp_Products:

                  LOAD Date(MakeDate(2012)+recno()-1) as Date,

                  'P'&ceil(RAND()*2) as Product,

                  ceil(RAND()*30) as Units

                  AutoGenerate 30;

                   

                  Products:

                  LOAD *,

                  if(AccUnits >= Lookup('TargetUnits','Product',Product,'Target'), 1,0) as Flag;

                  LOAD *

                  , if(Peek(Product)=Product,rangesum(Peek(AccUnits),Units), Units) as AccUnits

                  resident Temp_Products order by Product, Date;

                   

                  drop table Temp_Products;

                   

                  Then Expression in chart with dimensions Product:

                  =FirstSortedValue({<Flag={1}>} AccUnits, Date)

                  and

                  =min({<Flag = {1}>} Date