# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for
Did you mean:
Contributor III

## SUM Field A of DISTINCT Field B in a Date Range

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)))

6 Replies
Specialist

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

Contributor III
Author

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?

Specialist

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

Contributor III
Author

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

Contributor III
Author

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.

Specialist

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

Community Browser