Hello sir, stalwar1
I have a set of data in this format...
Date | Year | Month | Day | Location | Shop | Sales | Refund | Header 9 | Header 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