Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

;

8 Replies
Anonymous
Not applicable
Author

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
Champion
Champion

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
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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

Not applicable
Author

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

Anonymous
Not applicable
Author

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.

Not applicable
Author

Thank you.

barryharmsen
Luminary Alumni
Luminary Alumni

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!