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

[datanibbler]

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

[Anonymous]

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

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

Jonathan

[jvitantonio]

Hi, Use 1 in your set analysis:

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

[datanibbler]

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]

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]

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]

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 · Accepted solution]

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