topic Re: Use Cyclic Group Dimension in Expression in QlikView

Use Cyclic Group Dimension in Expression

MalcolmCICWF — Sat, 16 Nov 2024 18:14:08 GMT

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:

Re: Use Cyclic Group Dimension in Expression

marcus_sommer — Thu, 13 Aug 2020 13:32:36 GMT

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

Re: Use Cyclic Group Dimension in Expression

MalcolmCICWF — Mon, 17 Aug 2020 21:22:56 GMT

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.

Re: Use Cyclic Group Dimension in Expression

MalcolmCICWF — Fri, 21 Aug 2020 16:57:15 GMT

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

Re: Use Cyclic Group Dimension in Expression

Brett_Bleess — Tue, 25 Aug 2020 18:28:47 GMT

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/SetAnalysis/set-analysis-expressions.htm

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