Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
alexandergrando
New Contributor III

Convert This IF Statement to Set Analysis

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

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Convert This IF Statement to Set Analysis

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

8 Replies
CarlosAMonroy
Contributor III

Re: Convert This IF Statement to Set Analysis

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
New Contributor III

Re: Convert This IF Statement to Set Analysis

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:

IF Statement.PNG

Set Analysis:

Set Analysis.PNG

CarlosAMonroy
Contributor III

Re: Convert This IF Statement to Set Analysis

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

Re: Convert This IF Statement to Set Analysis

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

hope this helps

regards

Marco

MVP & Luminary
MVP & Luminary

Re: Convert This IF Statement to Set Analysis

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
New Contributor III

Re: Convert This IF Statement to Set Analysis

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
New Contributor III

Re: Convert This IF Statement to Set Analysis

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

please post a sample application to test with.

thanks

regards

Marco