Set analysis to if condition

[danny4202001]

Hello Friend,

The below query is giving perfect results as a set analysis but when I write this in script its gives wrong results data before May 

vNextmonth1= May

sum({$<AccYearPeriod={">=$(vNextmonth1)"} Quantity_OO *UNIT_SELLING_PRICE_OO)--- results are perfect

sum(if(AccYearPeriod>=($(vNextmonth1)), Quantity_OO * UNIT_SELLING_PRICE_OO)),'0' as FMTHGT3Open----------------------results are not right data includes from may and before 

Please help 

Thanks,

D

[Oleg_Troyansky]

Hi Danny,

It would be difficult to answer your question without a detailed look at your dataset. On the surface, both expressions should produce identical results (assuming that AccYearPeriod and AccYearMonth are the same and are linked to your transactions properly). I can only guess that most likely, there are some issues in the data model, that cause these fields to be associated to your transactions in different ways, which causes this problem.

As a troubleshooting solution, I'd recommend creating a table chart with these expressions and the fields AccYearPeriod and AccYearMonth as dimensions and spotting any unexpected outcomes - are these two values always the same? Is one of them NULL ? What other variances can you spot?

Also, allow me to invite you to the virtual Masters Summit for Qlik, specifically to my session about Set Analysis and AGGR. You will learn many advanced Set Analysis techniques, and how Set Analysis is compared to IF() conditions, etc... 

Cheers,

[danny4202001]

Hello Oleg,

Thank you for the reply, both AccYearPeriod and moth are the same yyyymm DIM and the requirement is to produce a pipe file on a daily basis so I can't use set analysis on the front end for this purpose.

Thanks,

D

[Oleg_Troyansky]

Hi Danny,

For a script calculation, check a few more things:

- Is the variable vNextmonth1 populated correctly at the time of the LOAD ? 

- if you period is formatted as YYYYMM, then it might be an alphabetic value. If it is, then the comparison value needs to be enclosed in single quotes: '$(vNextmonth1)'

- double check the data formats of both values. 

Cheers,

[jcmachado]

The difference between the two expressions you provided is that the first one uses set analysis with a variable, while the second one uses an if statement with a variable. It seems that the variable vNextmonth1 is not correctly referenced in the second expression. You can try to reference variable like this : sum(if(AccYearPeriod>=vNextmonth1, Quantity_OO * UNIT_SELLING_PRICE_OO)),'0' as FMTHGT3Open

Also, you can use the match function in your if statement to match the variable with the AccYearPeriod field, like this: sum(if(match(AccYearPeriod,vNextmonth1)>0, Quantity_OO * UNIT_SELLING_PRICE_OO)),'0' as FMTHGT3Open

It's also important to make sure that the variable vNextmonth1 is correctly defined and has the expected value in your script.

Please let me know if this helps, or if you have any other questions.

[Oleg_Troyansky]

Let me offer a little correction...

Variables have to be enclosed in $-sign expansions in order to be used in the LOAD statements in the script. So, the suggestion to use the variable without it wouldn't work. 

Either the value is not populated correctly prior to the LOAD, or the field and the variable have different data formats.

Cheers,