topic Re: Converting Date Range IF Statement to Set Analysis in QlikView

Converting Date Range IF Statement to Set Analysis

alexandergrando — Tue, 02 May 2017 16:39:44 GMT

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!

Re: Converting Date Range IF Statement to Set Analysis

m_woolf — Tue, 02 May 2017 16:44:49 GMT

The Basics about Dates, Date Ranges and Set Analysis

Re: Converting Date Range IF Statement to Set Analysis

sunny_talwar — Tue, 02 May 2017 16:46:06 GMT

May be like attached

Re: Converting Date Range IF Statement to Set Analysis

kaushiknsolanki — Tue, 02 May 2017 16:48:13 GMT

Hi,

Try this,

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

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

Regards,

Kaushik Solanki

Re: Converting Date Range IF Statement to Set Analysis

Anil_Babu_Samineni — Tue, 02 May 2017 16:50:57 GMT

May be these?

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

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

Re: Converting Date Range IF Statement to Set Analysis

alexandergrando — Tue, 02 May 2017 18:39:25 GMT

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.

Re: Converting Date Range IF Statement to Set Analysis

sunny_talwar — Tue, 02 May 2017 18:42:46 GMT

Check it out

Re: Converting Date Range IF Statement to Set Analysis

alexandergrando — Tue, 02 May 2017 18:51:26 GMT

Yes! That works. Thank you!

Re: Converting Date Range IF Statement to Set Analysis

alexandergrando — Wed, 03 May 2017 15:55:03 GMT

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.

Re: Converting Date Range IF Statement to Set Analysis

sunny_talwar — Wed, 03 May 2017 16:25:15 GMT

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?

Re: Converting Date Range IF Statement to Set Analysis

alexandergrando — Wed, 03 May 2017 18:43:09 GMT

Hi Sunny,

Thank you for your continued support. My goal is to use set analysis throughout my app since it requires less resources. The app is being designed to show important information related to property management of apartment complexes. This mostly includes metrics related to the COUNT of number of different properties managed by different dimensions like region, owner, etc. The same is the case with the SUM of units within each property. I have the sections of the app related to properties completed.

Now I need to finish the sections related to units. Most of the time, I can get by with using the following expression to SUM TotalUnitCount of each %PropertyKey where vMaxDate is a variable that takes the date of a calendar object assigned to a field called "CalendarDate" from a MasterCalendar dimension:

=SUM({<BegDateOfOperation={'<=$(vMaxDate)'}, EndDateOfOperation={'>$(vMaxDate)'}>} TotalUnitCount)

However, one section requires me to calculate the difference between unit counts from two different days specified by two calendar objects. I am able to successfully SUM one TotalUnitCount using the above expression for the calendar object assigned to "CalendarDate". But I am unable to get any results for the other calendar object assigned to the vAsOfDate variable. The variable vAsOfDate is not assigned to any field:

=SUM({<BegDateOfOperation={'<=$(vAsOfDate)'}, EndDateOfOperation={'>$(vAsOfDate)'}>} TotalUnitCount)

That is why I originally made this post. All I need is for a set analysis expression to SUM TotalUnitCount belonging to a calendar object assigned to vAsOfDate. You were almost able to provide a perfect solution with your expression, except that it ignores duplicates.

Re: Converting Date Range IF Statement to Set Analysis

sunny_talwar — Wed, 03 May 2017 20:36:08 GMT

I am starting to get some hang of it... is it possible that a single %PropertyKey can be associated with more than one BegDateOfOperation and/or EndDateOfOperation? If that may be the case, then you might benefit by creating a new field in the script like this

LOAD %PropertyKey,

BegDateOfOperation,

EndDateOfOperation,

AutoNumber(%PropertyKey & BegDateOfOperation & EndDateOfOperation) as NewKey,

TotalUnitCount

FROM

(ooxml, embedded labels, table is Sheet2);

and now use NewKey as your set analysis dimension

SUM({<NewKey = {"=BegDateOfOperation <= (vAsOfDate) and EndDateOfOperation > (vAsOfDate)"}>} TotalUnitCount)

Basically, the idea is to create a most granular key where we can check the inside conditon of

BegDateOfOperation <= (vAsOfDate) and EndDateOfOperation > (vAsOfDate)

The problem (from what I see is that) a single %PropertyKey might be associated with multiple BegDateOfOperation which is a problem. To see the problem create an object with the following

Dimension

%PropertyKey

Expressions

BegDateOfOperation

EndDateOfOperation

vAsOfDate

BegDateOfOperation <= (vAsOfDate)

EndDateOfOperation > (vAsOfDate)

You will see that whereever there are more than one BegDateOfOperation and/or EndDateOfOperation, one or both of your last two expressions are not able to give any output (will be null instead of 0 and -1). Our goal is to use a dimension where we can evaluate the last two expressions and based on that evaluation, pick those rows where we see -1. My hope is that once you add NewKey to your dimension (instead of %PropertyKey), you will start to see 0 and -1s for all the rows. If this is true, I am almost certain your issue should be resolved by using NewKey instead of %PropertyKey in your expression above

Re: Converting Date Range IF Statement to Set Analysis

alexandergrando — Wed, 03 May 2017 20:52:22 GMT

Hi Sunny,

You are absolutely right. The original and duplicate properties and their associated units are not being calculated because they have more than one BegDateOfOperation and EndDateOfOperation. I was able to confirm this via creating an object with the dimensions and expressions you mentioned.

However, a property should not have more than one BegDateOfOperation and EndDateOfOperation. The ones that do are the duplicates and are bad data that did not get filtered out properly. With this in mind, is there a possible solution without having to go into the script?

Re: Converting Date Range IF Statement to Set Analysis

sunny_talwar — Wed, 03 May 2017 20:58:15 GMT

You might be able to use Aggr() function to get the desired output, but if you want to use set analysis, you will require to make some changes in the script. Having said that, lets try what kaushik.solanki‌ provided below to see if this fits your requirement.

Sum({<BegDateOfOperation ={"<=$(vAsOfDate)"}, EndDateOfOperation ={">$(vAsOfDate)"}>} TotalUnitCount)

Basically, instead of focusing on %PropertyKey, we are directly restricting the expression to look at rows where BegDateOfOperation is less then or equal to vAsOfDate and EndDateOfOperation is greater than vAsOfDate.

See if this provides you with the output you are looking to get.

Re: Converting Date Range IF Statement to Set Analysis

alexandergrando — Wed, 03 May 2017 21:01:48 GMT

Unfortunately, Kaushik's solution did not work. I get zeroes as values.

Re: Converting Date Range IF Statement to Set Analysis

sunny_talwar — Wed, 03 May 2017 21:08:23 GMT

It did work for me in the sample you have attached... but dates in set analysis can cause a lot of problems.

You have to ensure that the date format inside your set modifier matches with the format your date field. If lets say the format of your date field is M/D/YYYY, but your variable gives a number, the set analysis will not work. So, if that is the case, you might need to throw in a date function like this

Sum({<BegDateOfOperation ={"<=$(=Date(vAsOfDate, 'M/D/YYYY'))"}, EndDateOfOperation ={">$(=Date(vAsOfDate, 'M/D/YYYY'))"}>} TotalUnitCount)

The best way to ensure what the dollar sign expansion is to remove the expression label

Once you remove it, the expression will show the evaluated version of the set analysis expression. See if this helps.

Re: Converting Date Range IF Statement to Set Analysis

alexandergrando — Wed, 03 May 2017 21:15:23 GMT

Yes! That worked! Thanks a thousand times, Sunny. I know I will be referring back to this thread for a long time to come.

Re: Converting Date Range IF Statement to Set Analysis

sunny_talwar — Wed, 03 May 2017 21:16:26 GMT

Awesome, I am glad we were able to help you out here