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!

[alexandergrando]

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.

[sunny_talwar]

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

[alexandergrando]

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?

[sunny_talwar]

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.

[alexandergrando]

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

[sunny_talwar]

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.

[alexandergrando]

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

[sunny_talwar]

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