No Set expression in If-functions?

[Applicable88]

Hello,

I found the following information on the qlik help site: 

The powerful thing of a Set Analysis is the endless possibility of combination dimensions and even function with Dollar Expansion. 

For instance if I want to show a KPI windows with the CURRENT monthly sales there are plenty of option to do so with Today() function.

How to use these in If-Statements. Dollar expansion needed? 

I know we can put if-statements in Set expression, but how to manage other way round?

Can you please provide examples?

Thanks in advance!

Best

[Lisa_P · Accepted solution]

Set Analysis is simpler and more efficient than using if statements.

There are plenty of posts and videos available on set analysis.

Also a great online tool to get the right syntax at:
http://tools.qlikblog.at/SetAnalysisWizard/QlikView-SetAnalysis_Wizard_and_Generator.aspx?sa=

[rwunderlich · Accepted solution]

Set expression is an alternative to an If() expression. You use one or the other.  Sets provide for conditional data selection just like if(). Here's an example. 

Sum(Sales)  // All Sales

Sum(If(InMonthToDate(OrderDate, Today(),0), Sales))  // If, Current Month

Sum({<OrderId={"=InMonthToDate(OrderDate, Today(),0)"}>} Sales). // Set, Current Month

There are some more optimized ways to approach the set expression, but I wanted to give a straight-line example using Today() as you mentioned. 

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[Applicable88]

Hello @rwunderlich ,

as always .....thanks for thorough explanation!

Thank you very much. 

Best.

[Applicable88]

Hi @rwunderlich,

one detail about what you showed me:

Sum({<OrderId={"=InMonthToDate(OrderDate, Today(),0)"}>} Sales). // Set, Current Month

Since "inmonthtodate" is a function, do I need to use "dollar expansion" in the set expression ? Or does it works here without the "$"? 

And in terms of a If-Statement, I guess there is no "$" needed for the inmonthtodate?

Best. 

[rwunderlich]

No, do not use Dollar Sign Expansion (DSE) in this case. DSE would result in the function calculating only once, you want it calculate for each row. 

-Rob