Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In the attached QVW I'm trying to extract data in the Orderbook tab where the snapshot_date is within the last 365 days.
I've created a variable vToday which is currently set on 90 to test but it still extracts data back to 23/06/2015
Even when I hard code the extract to extract where snapshot_date > '01/10/2015' is still pulls from 23/06/2015
The only was I can get this to work is to hard code with 10/01/2015, i.e. transposing the month and day so using MM/DD/YYYY
All the data coming out of SQL server is in DD/MM/YY format so I don't understand why this is.
Thanks in advance.
May bad, try this:
LET vToday = Date(floor(Today())-90,'MM/DD/YYYY');
LOAD
date(floor(snapshot_date), 'DD/MM/YYYY') as snapshot_date,
warehouse as orderbook_warehouse,
HPC_due_date as orderbook_due_date,
required_date as orderbook_required_date,
customer as orderbook_customer,
product as orderbook_product,
order_qty as orderbook_qty,
order_book_value as orderbook_value,
snapshot_date as orderbook_snapshot_date,
IF(HPC_due_date <= snapshot_date,'Y','N') as orderbook_arrears;
ODBC CONNECT TO [Sage PE Live];
SQL SELECT *
FROM pelive.dbo."hpc_op_orderbook_history"
where HPC_due_date < snapshot_date+90
and snapshot_date > '$(vToday)';
Can you try reloading with this:
LET vToday = Date(floor(Today())-90,'DD-MMM-YYYY');
LOAD
date(floor(snapshot_date), 'DD/MM/YYYY') as snapshot_date,
warehouse as orderbook_warehouse,
HPC_due_date as orderbook_due_date,
required_date as orderbook_required_date,
customer as orderbook_customer,
product as orderbook_product,
order_qty as orderbook_qty,
order_book_value as orderbook_value,
snapshot_date as orderbook_snapshot_date,
IF(HPC_due_date <= snapshot_date,'Y','N') as orderbook_arrears;
ODBC CONNECT TO [Sage PE Live];
SQL SELECT *
FROM pelive.dbo."hpc_op_orderbook_history"
where HPC_due_date < snapshot_date+90
and snapshot_date > '$(vToday)';
May bad, try this:
LET vToday = Date(floor(Today())-90,'MM/DD/YYYY');
LOAD
date(floor(snapshot_date), 'DD/MM/YYYY') as snapshot_date,
warehouse as orderbook_warehouse,
HPC_due_date as orderbook_due_date,
required_date as orderbook_required_date,
customer as orderbook_customer,
product as orderbook_product,
order_qty as orderbook_qty,
order_book_value as orderbook_value,
snapshot_date as orderbook_snapshot_date,
IF(HPC_due_date <= snapshot_date,'Y','N') as orderbook_arrears;
ODBC CONNECT TO [Sage PE Live];
SQL SELECT *
FROM pelive.dbo."hpc_op_orderbook_history"
where HPC_due_date < snapshot_date+90
and snapshot_date > '$(vToday)';
That's great, works a treat.
so basically transpose the date at the top, but the vital thing I had missed was the single speech marks around $(VToday)