Convert This IF Statement to Set Analysis

[alexandergrando]

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)))

[CarlosAMonroy]

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

[alexandergrando]

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:

[CarlosAMonroy]

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

[MarcoWedel]

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

hope this helps

regards

Marco

[Oleg_Troyansky · Accepted solution]

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

[alexandergrando]

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?

[alexandergrando]

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

[MarcoWedel]

please post a sample application to test with.

thanks

regards

Marco