topic Re: Convert This IF Statement to Set Analysis in QlikView

Convert This IF Statement to Set Analysis

alexandergrando — Wed, 19 Apr 2017 19:29:04 GMT

Hello. Can someone please tell me how to convert this IF statement expression to a set analysis expression? I've tried many different times, but am still unable to find one that works. Much appreciated!

=COUNT(DISTINCT(IF(BegDateOfOperation <= (CalendarDate) and EndDateOfOperation > $(vDateToday), %PropertyKey)))

Re: Convert This IF Statement to Set Analysis

CarlosAMonroy — Wed, 19 Apr 2017 19:42:32 GMT

Hi Alexander,

You can try the following:

=count({<BegDateOfOperation ={"<=$(=Date(CalendarDate),'MM/DD/YY')"}, EndDateOfOperation ={">$(=Date(Today()),'MM/DD/YY')"}>}%PropertyKey)

Hope that helps,

Carlos M

Re: Convert This IF Statement to Set Analysis

alexandergrando — Wed, 19 Apr 2017 20:10:41 GMT

Hi Carlos,

I tried your expression, but it seems like it is incorrect due to different results in my line chart. It looks like your expression is COUNTING all of the %PropertyKey regardless of date. I have attached some screenshots as reference:

IF Statement:

Set Analysis:

Re: Convert This IF Statement to Set Analysis

CarlosAMonroy — Wed, 19 Apr 2017 20:16:54 GMT

My expression does not have Distinct after the count. Did you try it adding the 'Distinct'?

Re: Convert This IF Statement to Set Analysis

MarcoWedel — Wed, 19 Apr 2017 20:17:37 GMT

=COUNT({$<%PropertyKey={"=BegDateOfOperation <= CalendarDate and EndDateOfOperation > $(vDateToday)"}>} DISTINCT %PropertyKey)

hope this helps

regards

Marco

Re: Convert This IF Statement to Set Analysis

Oleg_Troyansky — Wed, 19 Apr 2017 20:25:56 GMT

Hi Alexander,

unfortunately, this IF statement cannot be easily converted to Set Analysis, because Set Analysis cannot be sensitive to your Chart Dimensions - it is only calculated once, before the chart is calculated. You want you Set Analysis condition to be sensitive to the Date field, and you want your dimension to be Date-driven (Year and Month are nothing else but Date attributes...)

So, you can't convert your IF into Set Analysis directly.

You can, however, implement a solution known as "As of Date Table" and calculate certain conditional flags in the script that would make your condition more "static" and independent from the Date field. You can read about the As Of Date technique from many sources, including my blog:

QlikView Blog Q-Tip #4 How to Use "As of Date" table | Natural Synergies

You can also learn about it from my book QlikView Your Business. We also teach this topic, along with many other advanced data modeling techniques, at the Masters Summit for Qlik.

Cheers,

Oleg Troyansky

Re: Convert This IF Statement to Set Analysis

alexandergrando — Wed, 19 Apr 2017 20:26:13 GMT

I tried but a "No data to display" label appears instead of the chart. Can you retype the expression with DISTINCT so I am sure I got it right, please?

Re: Convert This IF Statement to Set Analysis

alexandergrando — Wed, 19 Apr 2017 20:28:42 GMT

This also did not work with a "No data to display" label appears instead of the chart. I think Oleg Troyansky is right below.

Re: Convert This IF Statement to Set Analysis

MarcoWedel — Wed, 19 Apr 2017 20:33:19 GMT

please post a sample application to test with.

thanks

regards

Marco