Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Skip the ticket, Chat with Qlik Support instead for instant assistance.
Showing results for 
Search instead for 
Did you mean: 
Partner - Contributor II
Partner - Contributor II

Calculate balance on last date by multiple dynamic dimensions

I'm trying to build a pivot table with dimensions that can be switched on and off that shows a balance of the last day of each month/year/quarter/half and aggregates correctly by all the different dimensions, however I'm having trouble using normal aggregation in a firstsorted value, my formula looks like this:

FirstSortedValue(AGGR(MAX(totalBalance),reading_date,Entity,Manufacturer,[Machine Number],[Serial Number],-AGGR(reading_date,reading_date,Entity,Manufacturer,[Machine Number],[Serial Number]))

However, the totals are showing blank like the below screenshot:


The balance shown for each month should be the last date of the month based on the field "reading_date"

Any ideas?

3 Replies
Partner - Master
Partner - Master


Can you give us an example QVF with some data? This makes it easier to test.

A possibility can be to use Dimensionality() if the function maybe doesn't work on aggregated levels.



Work smarter, not harder
Partner - Contributor II
Partner - Contributor II

Hi Jordy,

Thanks for responding to my query - I really appreciate it.

I've attached the exact problem with the real data - and added a bookmark to show my selections.

You'll notice that there's a filter dropdown to select dimensions and the measure also uses variables in the aggregation to allow for dynamic aggregation.

This is because the users of this dashboards constantly move around dimensions and measures on their pivot tables to customise their report. This is also the reason why I've been struggling using dimensionality - but I could definitely be mistaken.

The end goal is simply to show by any date "dimension" the last day of the day/week/month/year as it represents the final "balance" for a machine for that specific time period.

Partner - Contributor II
Partner - Contributor II

Perhaps I'm trying to achieve something that isn't possible - still haven't figured out a workaround.