Qlik Community

New to Qlik Sense

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

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: 
alwynkfinal
Partner
Partner

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:

QlikProblem.png

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

Any ideas?

Labels (1)
3 Replies
JordyWegman
Partner
Partner

Hi,

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.

Jordy

Climber

Work smarter, not harder
alwynkfinal
Partner
Partner
Author

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.

alwynkfinal
Partner
Partner
Author

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