Qlik Community

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 
Search instead for 
Did you mean: 
alexandergrando
Contributor III
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)))


Please help. I greatly appreciate it.

6 Replies
hector_munoz
Specialist
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

alexandergrando
Contributor III
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?

hector_munoz
Specialist
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

alexandergrando
Contributor III
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.

alexandergrando
Contributor III
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.

hector_munoz
Specialist
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