Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a mayor problem with a set_analysis trick that I've actually done several times before, only it just won't work and I cannot figure out why:
- I have several different date_values (named differently) in my document
- In the current list, values are populated until (yesterday).
- I have loaded a field with a date_value. By default, it is loaded in its numeric form, but I can also load it in date_format by using the DATE() fct in the script.
- When I manually select (yesterday) from a listbox with this date_field, I can display the correct value (from a field enclosed in []) in a textbox.
- I can also dynamically select from that textbox ("= Datum_Lauf=DATE(TODAY()-1)") and again I get the correct value in my textbox.
- Last step -> it's there that I fail: I want to write a set_analysis expression to display yesterday's value - the last one populated in the list - without making any selection. This is what I am doing:
= sum({<Datum_Laufprüfung = {$(=DATE(TODAY()-1))}>} [Überprüfte Teile])
Can anybody tell me why this isn't working?
Thanks a lot!
Best regards,
DataNibbler
Yes, today() returned an integer - cut off with floor is the right approach.
Try putting single quotes around the date element of the expression:
sum({<Datum_Laufprüfung = {'=$(=DATE(TODAY()-1))'}>} [Überprüfte Teile])
Jonathan
Hi, Use 1 in your set analysis:
= sum({1<Datum_Laufprüfung = {$(=DATE(TODAY()-1))}>} [Überprüfte Teile])
Hi,
it is really mysterious:
I have combined your two proposals and I wrote:
= sum({1<%Datum = {'=$(=DATE(TODAY()-1))'}>} [Überprüfte Teile])
<-> The textbox now says 0 😉 At least it does display a value, but that's wrong - I can check in the original list and I see that there is a value of 3170 on that date.
I'm really baffled.
P.s.: What is even more mysterious: Just one line above, I have a similar set_analysis_expression, merely using another date_field - there I get the correct value. Using the exact same syntax now, it works - but the result is 0, regardless of formatting.
I assume Datum and Date(Today()) have not the same format - Date without parameters returned the default-format.
- Marcus
Hi Marcus,
I think I have tried all possible variations of the formatting thing in the meantime - I have tried using the DATE() fct in the script and comparing either with TODAY() or DATE(TODAY()) - the result is the same.
Anyway, I'll try again.
Best regards,
DataNibbler
Another way is the using from numbers instead dates - this avoid a lot of problems. Anyway have you tried it with fixed-values like {'2013-06-18'} to see the formula worked properly up to the function?
- Marcus
I'm giving up - I'll try again tomorrow.
With another scenario, I can do it all right, proving that I'm not too stupid to get the syntax right, generally speaking - the difficult part is that dynamic_expression inside the set_identifier. I have also tried replacing that by a variable, to no avail.
P.S.: I just notice I seem to have missed your latest post. Happens sometimes. Yes - I have a different default_date_format set (in the MAIN tab of the script), namely DD.MM.YYYY (german notation), but I've tried.
Hi,
I'm still struggling here. I have thought that maybe a trick I had introduced in the LOAD statement was malfunctioning, but since it works with separate listboxes, QlikView is apparently loading the right values.
Strangely, the date (numeric value) is loaded with 7 decimals. Maybe I have to use the FLOOR function to adapt it to the default numeric format of TODAY()?
Yes, today() returned an integer - cut off with floor is the right approach.