Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try the following expression:
=Sum({$<Date={"<=Date(Capture_Date+($(vRollPeriod)*7))"}>}Sales_Retail)
No, unfortunately that is not working. I will attach the .qvw for better context.
.qvw attached
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!
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.
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:
Re: If statement vs set analysis
Background Chart Expression Color using Set Analysis
Evaluating "sets" in the context of a dimension
Sum of others using set analysis
Re: Gap between "if" and set analysis
Miguel
you can try this
=Sum({$<Date={"<=Date(Capture_Date+($(=vRollPeriod)*7))"}>}Sales_Retail)
i thought the problem is about vRollPeriod variable
Try making a variable and perform the calculation [ Capture_Date+(vRollPeriod*7) ] in the variable and return it into the set analysis expression.