topic Re: Set analysis with date variable in QlikView

Set analysis with date variable

frank1982 — Mon, 06 Apr 2020 09:35:06 GMT

Hi everyone,

I need help to build a set analysis. I have the following data:

1. A Field DATA (the reference date of my data);

2. A variabile vDataFore (a date -give from an input box- that I have to compare with field DATA);

3. A Field LOAD1 (the data that I have to analyze).

The target of my analysis is to visualize, into a table, the difference between:

LOAD_of_row_Date - LOAD_of_vDataFore_Date

To obtain this, I wrote the set analysis (in column D-LOAD1)

=Sum(LOAD1)-Sum({<DATA={"=$(vDataFore)"}>}LOAD1)

But the result is

Where is the mistake? Someone could help me?

Thanks in advance.

Re: Set analysis with date variable

frank1982 — Mon, 06 Apr 2020 10:24:02 GMT

I forgot to specify that vDataFore has this value:

Re: Set analysis with date variable

tresB — Mon, 06 Apr 2020 10:41:27 GMT

Try removing '=' in set modifier like:

=Sum(LOAD1)-Sum({<DATA={'$(vDataFore)'}>}LOAD1)

Also, make sure that you variable date format is same as your date field DATA.

Re: Set analysis with date variable

frank1982 — Mon, 06 Apr 2020 10:58:52 GMT

Hi Tresesco,

thanks for your answer, I applied your suggestion to my table, but the result is the following

I added the column Test = if(DATA=vDataFore,'si','no') to verify the format Date.

I though that the problem could be related to the format of Dates, but I don't have evidence of this.

Do you have any other suggestion?

Thank you

Re: Set analysis with date variable

tresB — Mon, 06 Apr 2020 12:07:05 GMT

Comparison in IF and Set Analysis are not always similar. Set analysis comparison is always based on literals/string unlike IF. So, though you can validate that if gets it right, it might not be same with set analysis that demands format match. Could you share your sample app? Otherwise try another variation like:

=Sum(LOAD1)-Sum({<DATA={"$(=Date(vDataFore, 'DD-MM-YYYY'))">}LOAD1)

Re: Set analysis with date variable

frank1982 — Mon, 06 Apr 2020 13:37:19 GMT

The actual result of my table is

Now the difference is done only for the row where DATA=vDataFore

I would like the difference for each row, instead.

I share a light version of my app and the data to upload (it is necessary to unzip the file and change the Path into the Load).

Re: Set analysis with date variable

tresB — Mon, 06 Apr 2020 13:41:00 GMT

Add 'TOTAL' in your second sum(), like:

=Sum(LOAD1)-Sum(total {<DATA={"$(=Date(vDataFore,'DD-MM-YYYY'))"}>}LOAD1)

Re: Set analysis with date variable

frank1982 — Mon, 06 Apr 2020 14:17:54 GMT

Ok, now it works!

Thank you very much!