Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
alexandergrando
Contributor III
Contributor III

Converting Date Range IF Statement to Set Analysis

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!

1 Solution

Accepted Solutions
sunny_talwar

May be like attached

View solution in original post

17 Replies
sunny_talwar

May be like attached

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

Try this,

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

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

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Anil_Babu_Samineni

May be these?

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

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
alexandergrando
Contributor III
Contributor III
Author

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

Yes! That works. Thank you!

alexandergrando
Contributor III
Contributor III
Author

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?