Set Analysis Issue

[#1]

I am challenged w a Set Analysis expression.

The following Set Analysis expression is returning NO values;

=Sum({$<Date={"<=Capture_Date+(vRollPeriod*7)"}>}Sales_Retail)

Whereas the following IF expression IS returning the expected values;

=Sum(If(Date<=Capture_Date+(vRollPeriod*7),Sales_Retail,0))

Any clues as to why the Set Analysis expression will not return a value?

To add some context, the intent is to provide visibility to Customer Purchases ("Sales_Retail") for a select period ("vRollPeriod") since they were registered ("Capture_Date"). "Date" represents the date of a purchase. Further, "vRollPeriod" is a selection of value that represents a No of Weeks.

Thank you in advance for any help.

[nagaiank]

Try the following expression:

=Sum({$<Date={"<=Date(Capture_Date+($(vRollPeriod)*7))"}>}Sales_Retail)

[#3]

No, unfortunately that is not working. I will attach the .qvw for better context.

[#4]

.qvw attached

[Miguel_Angel_Baeyens]

Hi Kent,

In this case, Set Analysis will not work. Set Analysis is calculated once for the whole chart, before it is rendered, therefore its speed. But in your case, what you want to do is a column by column comparison, not a set analysis. Depending on the value in one column you want to get different values in the other, and thus for each row. You If() solution is good, and probably the only one here, apart from doing something in the script.

Moreover, you are using calculated dimensions, that need to be solved before the set and that will only work with If() and not Set Analysis

Miguel

P.S.: Do not upload so big files, took long to download. A dozen customers would have done the same!

[#6]

I'm not clear on how I am wanting to do a column by column comparison. I do have a few columns visible that display the components of the expression-- this is for demonstration purposes. Those will go away once I get the Set Analysis expression right. I find it hard to believe that a Set Analysis expression cannot be used in place of the IF expression.

[Miguel_Angel_Baeyens]

Hi Kent,

This has been long discussed in Qlik Community since the introduction of Set Analysis in later version 8.5. Here you are some of the links that provide some insights on when to use If() and when Set Analysis, and why:

Set analysis help

Re: If statement vs set analysis

Background Chart Expression Color using Set Analysis

Basic Set Analysis Question

Evaluating "sets" in the context of a dimension

Sum of others using set analysis

Set analysis: ColA &lt;= ColB

Re: Gap between "if" and set analysis

Miguel

[menta]

you can try this

=Sum({$<Date={"<=Date(Capture_Date+($(=vRollPeriod)*7))"}>}Sales_Retail)

i thought the problem is about vRollPeriod variable

[hannan_t]

Try making a variable and perform the calculation [ Capture_Date+(vRollPeriod*7) ] in the variable and return it into the set analysis expression.