How can I manipulate a variable date I set in the query on the report sheet

[#1]

Hello ,

Please I have two variable dates in my query and when I run it it gives me a blank report even after I set the dates to a default. My question is please can I have like a text box to change these dates (Like a date range) from the report sheet.

LET vtotal_date_from = '2013-11-30';

LET vtotal_date_to = '2013-12-31';

SELECT

customer.entity_id AS customer_id,

firstname.value AS firstname,

lastname.value AS lastname,

customer.`email` AS email,

phone.value AS telephone,

pu.created_at AS total_verify_date

FROM customer_entity customer

LEFT JOIN `customer_entity_varchar` firstname ON (firstname.entity_id = customer.entity_id AND firstname.attribute_id = 5)

LEFT JOIN `customer_entity_varchar` lastname ON (lastname.entity_id = customer.entity_id AND lastname.attribute_id = 7)

LEFT JOIN `customer_address_entity_varchar` phone ON (phone.entity_id = customer.entity_id AND phone.attribute_id = 31)

INNER JOIN (SELECT customer_id , sales_flat_order_status_history.`created_at`,

MIN(sales_flat_order_status_history.`entity_id`) AS entity_id

FROM sales_flat_order

LEFT JOIN sales_flat_order_status_history ON sales_flat_order_status_history.parent_id = sales_flat_order.entity_id

WHERE ((sales_flat_order_status_history.status LIKE '%verified%' AND sales_flat_order_status_history.status NOT LIKE '%canceled%'

AND sales_flat_order.created_at > '2013-11-01')

OR (sales_flat_order.status LIKE '%complete%' AND sales_flat_order.created_at < '2013-11-01'))

AND sales_flat_order_status_history.`created_at` >= $(vtotal_date_from)

AND sales_flat_order_status_history.created_at <= $(vtotal_date_from)

AND sales_flat_order.customer_id IS NOT NULL

GROUP BY sales_flat_order_status_history.`parent_id`) AS pu ON pu.customer_id = customer.entity_id

;

[Anonymous]

First thing to try is the single quotes:

AND sales_flat_order_status_history.`created_at` >= '$(vtotal_date_from)'

AND sales_flat_order_status_history.created_at <= '$(vtotal_date_from)'

[datanibbler]

Hi John,

if you deactivate those two WHERE_bits (those rgd. the variables) from your SELECT_statements, does it work?

=> Test the two variables in a text_box on the GUI first to see whether you have to reference them with $() or without (I'd guess without for they hold only one value each, not an expression)

[jonathandienst]

In addition to the quotes, make sure the dates are in a format the database recognises. Try running the query with the dates hard coded to make sure the query is running correctly, and then add the date variables.

[#5]

I don't really understand. Please can you explain more.

[#6]

Hello Guys, the problem I'm having is can I reset these variables on the sheet to another date. Thank you.

[Anonymous]

John,

All you need is the input box for these two variables, and remove LET statements from the script, otherwise they'll override what you have in the input box.

[#8]

Thank you.

[barryharmsen]

Just an add-on, another nice trick is to use the WHEN statement in your script to set the variable to a default date if it wasn't entered in the input box:

WHEN '$(vtotal_date_from)' = '' LET vtotal_date_from = '2013-11-30';

The reverse statement is UNLESS. More info can be found here: WHEN and UNLESS » The Qlik Fix! The Qlik Fix!