Converting Date Range IF Statement to Set Analysis

[alexandergrando]

Hi everyone,

I have a straight table that calculates the difference between active properties on one date vs. active properties on another date. Currently, I am using an IF statement in the expression to first determine whether a property was active on a date. Instead, I would like to use set analysis to do the very same thing. Attached is a sample app with sample data. Any help would be greatly appreciated!

[m_woolf]

The Basics about Dates, Date Ranges and Set Analysis

[sunny_talwar · Accepted solution]

May be like attached

[kaushiknsolanki]

Hi,

Try this,

Count(  {<BegDateOfOperation ={"<=$(vAsOfDate)"}, EndDateOfOperation ={">$(vAsOfDate)"}>}Distinct %PropertyKey)

Count(  {<BegDateOfOperation ={"<=$(=num(vMaxDate))"}, EndDateOfOperation ={">$(=num(vMaxDate))"}>} distinct %PropertyKey)

Regards,

Kaushik Solanki

[Anil_Babu_Samineni]

May be these?

1) Count({<BegDateOfOperation = {'<= $(vAsOfDate)'}, EndDateOfOperation = {'> $(vAsOfDate)'}>}%PropertyKey)

2) Count(  {<BegDateOfOperation ={"<=$(=num(vMaxDate))"}, EndDateOfOperation ={">$(=num(vMaxDate))"}>} distinct %PropertyKey)

[alexandergrando]

Hi Sunny,

Thank you taking the time to look at my app and provide a correct answer! If it's not too much to ask, can you take a look at another one of my straight tables for a similar problem, please?

This time, I'm trying to SUM TotalUnitCount instead of COUNT %PropertyKey. The problem is that I either get zero or a number higher than the total sum when I try using set analysis. Can you please convert the IF statements to set analysis again? Attached is a new sample app with updated sample data.

EDIT: It's probably worth mentioning that TotalUnitCount belongs to a %PropertyKey. So, an aggregate statement is probably needed, but I can't figure it out.

[sunny_talwar]

Check it out

[alexandergrando]

Yes! That works. Thank you!

[alexandergrando]

Hi again, Sunny,

I hate to bother you again, but your code started acting strangely upon closer examination. I noticed that none of a specific %PropertyKey will be counted if there is more than one occurrence of that %PropertyKey once applied on the entire data set. The funny thing is that I was unable to replicate the results on the smaller data set I provided even after going in and creating duplicates in it.

Can you kindly provide a few alternative set analysis expressions to SUM TotalUnitCount belonging to %PropertyKey, please? I am unable to figure out how to include an aggregation in set analysis because I am still very new to QlikView. Thanks again.

[sunny_talwar]

Yes, that is true... I gave the expression believing that you are checking the sum of TotalUnitCount on %PropertyKey level.... but if you are not and duplicate %PropertyKey are causing issues for you then you will have to look for an alternative. What that alternative can be would depend on your actual data and need. Would you be able to elaborate the business requirement a little more explaining what exactly are you looking to get?