Nested set Analysis

[DesmoArka]

Hi,

i have this expression that works fine in qlikview:

sum({<DTREG={'<=$(=DATE(MAX(DTREG)))'},ID_FATTURA={"=fabs(round(sum({<DTREG={'<=$(=DATE(MAX(DTREG)))'}>}DARE)-sum({<DTREG={'<=$(=DATE(MAX(DTREG)))'}>}AVERE),0.01))>0"}>}DARE)

It doesn't work on cloud.

I see that the formula

sum({<DTREG={'<=$(=DATE(MAX(DTREG)))'}>}DARE) works if written in this way:

sum({<DTREG={"<=$(=DATE(MAX(DTREG)))"}>}DARE)

The problem is in this piece:

..ID_FATTURA={"=fabs(round(sum({<DTREG={'<=$(=DATE(MAX(DTREG)))'}>}DARE)-sum({<DTREG={'<=$(=DATE(MAX(DTREG)))'}>}AVERE),0.01))>0"}

if i change ' with " i have expression error because there are to formulas nested.

Some advise?

Thanks a lot.

[p_verkooijen · Accepted solution]

Hi @DesmoArka 

This likely because the "bug" that you can use ' for a " has been fixed for a while, only applied in new documents. https://community.qlik.com/t5/New-to-Qlik-Analytics/Set-Analysis-QlikView-12-vs-Qlik-Sense/m-p/24825... 

In your case, you have to use [] instead of "" the inner SA search *

sum(
{<
DTREG={"<=$(=DATE(MAX(DTREG)))"},
ID_FATTURA={"=fabs(sum({<DTREG={[<=$(=DATE(MAX(DTREG)))]}>}DARE)-sum({<DTREG={[<=$(=DATE(MAX(DTREG)))]}>}AVERE))>0"}
>}
DARE)

* changed expression based on Hic's reply

Drop the ROUND(,0.01), does nothing in regard to > 0

[hic · Accepted solution]

Don't use single quotes - that means literals, i.e. not expressions.

To quote searches you can nest set analysis in three levels:

Double quotes: "..."
Square brackets: [...]
Grave accents `...`

Use them one by one in the three levels.

See also 
https://community.qlik.com/t5/Design/QlikView-Quoteology/ba-p/1476029
https://community.qlik.com/t5/Design/Quotes-in-Set-Analysis/ba-p/1471824