Announcements
Sept. 27, 10AM ET Do more with Qlik, Insight Advisor – our intelligent AI-assistant in Qlik Sense: Register
cancel
Showing results for
Did you mean:
Creator III

## Use Cyclic Group Dimension in Expression

I am trying to get the percent (Rate) by month (shown as 1 and 2). It's the Count of that month divided by the total for that PoolPlusID (show highlighted green).  Ex. 235 / 116,093 for .20%... I'm using Count \ SUM(total COUNT_ACCT_ID) and it works for that one row selected, but once I select another record (show in 2nd screenshot) it changes the the Rate from .20% to .19% because it adds 6,704 to the total. I want it always to divide by the total for it's individual PoolPlusID... Any ideas?

Doesn't Work:

Labels (9)

• ### Sum Total

4 Replies
MVP & Luminary

To ignore/overwrite the set selections you need to include a set analysis - maybe something like this:

Count / SUM({< Record = >} total COUNT_ACCT_ID)

- Marcus

Creator III
Author

Doesn't seem to be working, it drastically changed the  Rate/percentage.

I edited my original Question to hopefully better explain. The calculation works on a per PoolPlusID selection, but once I select another, the 235 is divided by both totals (122,797 = 116,093+6,704), not just the 116,093

Would some sort of Aggr function work to do it by PoolPlusID?

I had SOME success getting the 116,093 total using the below:

(my count of 225)   /  Sum(Aggr( Count(COUNT_ACCT_ID), POOLPLUSID))

But then this only gives me a Rate(percentage) for the first month, my other months are blank.

Creator III
Author

Can anyone else help me solve this? Still can't get it to work unless I drill down to that specific selection.

Former Employee

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/ChartFunctions/...

If you want further direct assistance, I would recommend attaching the QVW file to the thread, as that will be the most useful thing, as people can then see the full data model and your expressions etc. as they are being used in the object.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Community Browser