Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Partner
Partner

Sum latest values, but not over years

We have an issue that I'm not sure we can solve:

We have one value per building per month.

This is the current value at the time, so each building should show the latest value.

The buildings are collected into groups - these should show the sum of all buildings (latest value each).

example.PNG

However, I can't get it to work.

Here's what I have so far:

Sum(Aggr(FirstSortedValue([Value], -[enddate]), [Building], Year))

This leads to the result as shown above. The years are right, but the building is a SUM of the years, but it should be only the latest.

If I leave out the "Year" in the Aggr-function, the sum is right, but I only get one value per building, not one per year per building (when drilling down).

What I need is a function which returns:

The sum of the latest value value per building for all buildings in a group (with varying hierarchies), per year in this visualization. 

I hope that makes sense, maybe someone more experienced knows of a solution to this.