Set analysis with date variable

[frank1982]

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.

[frank1982]

I forgot to specify that vDataFore has this value:

[tresesco]

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.

[frank1982]

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

[tresesco]

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)

[frank1982]

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).

[tresesco · Accepted solution]

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

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

[frank1982]

Ok, now it works!

Thank you very much!