topic Re: Set Analysis Issue in QlikView

Set Analysis Issue

Sun, 10 Aug 2014 01:52:55 GMT

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.

Re: Set Analysis Issue

nagaiank — Sun, 10 Aug 2014 02:24:42 GMT

Try the following expression:

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

Re: Set Analysis Issue

Sun, 10 Aug 2014 11:56:47 GMT

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

Re: Set Analysis Issue

Sun, 10 Aug 2014 12:08:31 GMT

.qvw attached

Re: Set Analysis Issue

Miguel_Angel_Baeyens — Sun, 10 Aug 2014 16:11:31 GMT

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!

Re: Set Analysis Issue

Sun, 10 Aug 2014 21:32:30 GMT

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.

Re: Set Analysis Issue

Miguel_Angel_Baeyens — Mon, 11 Aug 2014 09:36:01 GMT

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 <= ColB

Re: Gap between "if" and set analysis

Miguel

Re: Set Analysis Issue

menta — Mon, 11 Aug 2014 10:45:57 GMT

you can try this

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

i thought the problem is about vRollPeriod variable

Re: Set Analysis Issue

hannan_t — Mon, 11 Aug 2014 10:48:11 GMT

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