Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
MalcolmCICWF
Creator III
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?

Capture.PNG

Doesn't Work:

Capture.PNG

4 Replies
marcus_sommer

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

MalcolmCICWF
Creator III
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. 

MalcolmCICWF
Creator III
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.

Brett_Bleess
Former Employee
Former Employee

Per Marcus' comment, the following links should be helpful:

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.