Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
datanibbler
Champion
Champion

Set analysis - it should be quite easy, but... Plz help!

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

1 Solution

Accepted Solutions
marcus_sommer

Yes, today() returned an integer - cut off with floor is the right approach.

View solution in original post

12 Replies
Anonymous
Not applicable

Try putting single quotes around the date element of the expression:

sum({<Datum_Laufprüfung = {'=$(=DATE(TODAY()-1))'}>} [Überprüfte Teile])

Jonathan

jvitantonio
Luminary Alumni
Luminary Alumni

Hi, Use 1 in your set analysis:

= sum({1<Datum_Laufprüfung = {$(=DATE(TODAY()-1))}>} [Überprüfte Teile])

datanibbler
Champion
Champion
Author

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.

marcus_sommer

I assume Datum and Date(Today()) have not the same format - Date without parameters returned the default-format.

- Marcus

datanibbler
Champion
Champion
Author

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

marcus_sommer

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

datanibbler
Champion
Champion
Author

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.

datanibbler
Champion
Champion
Author

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()?

marcus_sommer

Yes, today() returned an integer - cut off with floor is the right approach.