topic Re: Derive count based on dupcount in Visualization and Usability

Derive count based on dupcount

shivi_ — Mon, 27 May 2024 11:36:33 GMT

HI,

I have a table where we have patient data and respective duplicate count created in backend. Now the table looks like below with other columns. I want to have a sum of non unique entries for per subject where dupcount is greater than 1. Pls help

SubjectNO. dupcount

123 1

123 2

123 22

234 4

234 5

234 6

234 1

345 4

456 4

457 1

567 1

567 2

567 3

Re: Derive count based on dupcount

Chanty4u — Mon, 27 May 2024 11:58:05 GMT

Try this

Sum({<dupcount = {"=dupcount > 1"}>} dupcount)

Re: Derive count based on dupcount

shivi_ — Mon, 27 May 2024 15:11:44 GMT

Thankyou for your reply 😁

Sorry but the above does not gives the expected result. Currently I am very new to Qlik and use basic functions only. In my screenshot you can see my formula gives output for the 1st record but also print the same value for other subjects too. Can we add a group by too pass the alternate dimensions that might serve the purpose.

Re: Derive count based on dupcount

edwin — Mon, 27 May 2024 16:35:36 GMT

a little adjustment:
Sum({<dupcount = {">1"}>} dupcount)

Re: Derive count based on dupcount

shivi_ — Tue, 28 May 2024 04:06:45 GMT

The above gives the same output as suggested by Chanty. See the png file for more details.

Re: Derive count based on dupcount

edwin — Wed, 29 May 2024 16:52:42 GMT

your problem statement is wrong, from your pic, you are summing 1 and not the field - maybe what you need is a count or

Sum({<dupcount = {">1"}>} 1)

Re: Derive count based on dupcount

edwin — Wed, 29 May 2024 16:54:48 GMT

you maybe need to reevaluate what you want - you may want the count of SubjectNo - also depending on both your data model and requirements, you may want a count distinct

count(distinct {<dupcount = {">1"}>} SubjectNo)