topic Re: How can I manipulate a variable date I set in the query on the report sheet in Archived Groups

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

Tue, 22 Jul 2025 17:59:49 GMT

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

;

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

Anonymous — Fri, 14 Mar 2014 14:07:09 GMT

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)'

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

datanibbler — Fri, 14 Mar 2014 14:10:07 GMT

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)

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

jonathandienst — Fri, 14 Mar 2014 14:10:55 GMT

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.

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

Fri, 14 Mar 2014 14:26:06 GMT

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

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

Fri, 14 Mar 2014 14:29:47 GMT

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

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

Anonymous — Fri, 14 Mar 2014 14:36:57 GMT

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.

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

Fri, 14 Mar 2014 16:38:05 GMT

Thank you.

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

barryharmsen — Sat, 15 Mar 2014 14:40:43 GMT

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!