SUM Field A of DISTINCT Field B in a Date Range

[alexandergrando]

Hi. I am trying to SUM the TotalUnitCount of active properties within a date range. I am not sure how to write an expression to do this.

When I try this expression, I end up summing TotalUnitCount belonging to duplicate properties since a SUM takes place for each date within range:

=SUM(IF(BegDateOfOperation <= (CalendarDate) and EndDateOfOperation > (CalendarDate), TotalUnitCount))

When I try this expression, I end up omitting any TotalUnitCount with the same number:

=SUM(DISTINCT(IF(BegDateOfOperation <= (CalendarDate) and EndDateOfOperation > (CalendarDate), TotalUnitCount)))

Please help. I greatly appreciate it.

[hector_munoz]

Hi Alexander,

Withput any knowledge of your data model, maybe what you could do to solve your problem is: first calculate the average TotalUnitCount per Operation (I assume you have a field like Operation ID) and later sum all the averages. This could be solved using an Aggr() function:

Sum(Aggr(Avg(IF(BegDateOfOperation <= (CalendarDate) and EndDateOfOperation > (CalendarDate), TotalUnitCount)), Operation_ID))

Hope it serves!

Regards,

H

[alexandergrando]

Hi Muñoz Héctor,

I don't think I have an Operation ID. I do have a Property ID though. I used Property ID to calculate the number of active properties within a date range with this expression:

=COUNT(DISTINCT(IF(BegDateOfOperation <= (CalendarDate) and EndDateOfOperation > (CalendarDate), %PropertyKey))).

Now my goal is to SUM the TotalUnitCount belonging to those properties. Does that help at all?

[hector_munoz]

Hi Alexander,

You can obtain the sum of all TotalUnitCount with the following formula:

Sum(IF(BegDateOfOperation <= CalendarDate and EndDateOfOperation > CalendarDate, TotalUnitCount))

Note that if a property is counted in your formula is because CalendarDate is among its operation date range (BegDateOfOperation and EndDateOfOperation)

Regards,

H

[alexandergrando]

As I mentioned in the original post, I've already tried that formula. It ends up summing units belonging to the same property.

[alexandergrando]

Hi Muñoz Héctor,

I went back and took a closer look at the results of using the expression: Sum(IF(BegDateOfOperation <= CalendarDate and EndDateOfOperation > CalendarDate, TotalUnitCount))

It looks like the results are correct if I am also using CalendarDate as the dimension in my line chart. However, I need to use a cyclical group of date granularity in my chart. The problem is that TotalUnitCount for the same property becomes counted multiple times once I switch date granularity for the dimension to something like CalendarYear.

I am pretty sure that what I need to do is use an AGGR statement. But I don't know how to do this. Do you have any more ideas? Please let me know.

[hector_munoz]

Hi Alexander,

If you do the following:

Sum(Aggr(Avg(IF(BegDateOfOperation <= (CalendarDate) and EndDateOfOperation > (CalendarDate), TotalUnitCount)), Property))

, you will calculate de average of TotalUnitCount per Property, and then you will solve your duplicates problem, and later you will sum all the avergaes.

Would it work for you? If donot, could you attach a sample?

Regards,

H