0 Replies Latest reply: May 11, 2017 6:38 AM by Akpofure Enughwure RSS

    Peak Sales Values per Day

    Akpofure Enughwure

      Hello sir,  stalwar1

      I have a set of data in this format...

       

       

      DateYearMonthDayLocationShopSalesRefundHeader 9Header 10

       

      Thanks to

      roharoha

       

      I have managed to get the sales at the latest date as well as the total sum using this script

       

      LIB CONNECT TO 'XXXX';

      [Sales Table]:

      LOAD date(Floor( "Date"),'DD/MM/YYYY') as "Date",

          Location  ,

         Shop,

           Sales,

           Refunds;

      SQL SELECT "Date",

          Location,

      Location  ,

         Shop,

           Sales,

           Refunds;

      FROM "NPDC BI".dbo."DAILY  GAS";

       

      [Sales]:

      LOAD

      Location  as [Primary Location],

         Shop as [Main Shop],

           Sales as [Main Sales],

           Refunds;

       

            

      resident [Sales Table]; // to pick the gas production only

       

      TEMP: LOAD max("Date") as MAXDATE resident [Sales Table]; // to pick the latest values as in latest date

       

      let varmaxdate = peek('MAXDATE');

       

      drop Table TEMP;

       

       

      Noconcatenate

       

      DailySalesTABLE:

       

      LOAD * resident [Sales Table]

       

      where Date=$(varmaxdate);

       

       

      drop table [Sales Table];

       

      let varmaxdate=null();

       

      The above script produces...  the sales figures at the latest date....

       

       

      I will look to produce the peak sales per day.... ( higest sales per day) in the given period.

       

      Regards