topic Count distinct in Set Analysis in QlikView

Count distinct in Set Analysis

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hiya,

I'm trying to count a total number of checks the number of checks within amount ranges 0 -2500 and 2501 - 5000, so on and so forth. I have what i thought was correct but it seems to be counting individual amounts and not check numbers. Any thoughts on where I am wrong in my set analysis.

=count(DISTINCT(If([CHK_NO] >= 0 and [PAID_AMT]<= 2500, [PAID_AMT])))
=count(DISTINCT(If([CHK_NO] >= 2501 and [PAID_AMT]<= 10000, [PAID_AMT])))
=count(DISTINCT(If([CHK_NO] >= 10001 and [PAID_AMT]<= 1000000, [PAID_AMT])))

Below is the data result i am getting i am using a test of one check and see how i am getting different counts per line and not rolled up to the check number. I would expect to see o in the first column and 1 in the second

Re: Count distinct in Set Analysis

kfoudhaily — Mon, 02 Jul 2018 15:27:57 GMT

first one:

Count({$<

[CHK_NO]={">=0"},

[PAID_AMT]={"<=2500"}

distinct([PAID_AMT])

)

Re: Count distinct in Set Analysis

Anil_Babu_Samineni — Mon, 02 Jul 2018 15:49:15 GMT

May be try this?

=If([CHK_NO] >= 0 and [PAID_AMT]<= 2500, If([CHK_NO] >= 2501 and [PAID_AMT]<= 10000, If([CHK_NO] >= 10001 and [PAID_AMT]<= 1000000, Count(DISTINCT PAID_AMT))))

=Sum(Aggr(If([CHK_NO] >= 0 and [PAID_AMT]<= 2500, If([CHK_NO] >= 2501 and [PAID_AMT]<= 10000, If([CHK_NO] >= 10001 and [PAID_AMT]<= 1000000, Count(DISTINCT PAID_AMT)))),CHK_NO))

Re: Count distinct in Set Analysis

Anonymous — Mon, 02 Jul 2018 15:55:24 GMT

Thank you for the reply, that seemed to remove all filters and do a sum of all checks, I am attempting to try a different version of your suggestion. Thank you again for the reply

Re: Count distinct in Set Analysis

Anonymous — Mon, 02 Jul 2018 15:57:14 GMT

Thank you for the reply, I am slightly confused, if i may inquire some more, should all the calculations be the same and the check count will fall into place? This is a little hard for me to understand, do i break up each segment into a different expression?

Re: Count distinct in Set Analysis

Anil_Babu_Samineni — Mon, 02 Jul 2018 16:01:20 GMT

Whatever, You have done that is exactly how Qlik returns. But, I don't know why not returning any values for you. For assumption, CHK_NO and PAID_AMT are associated each??

Re: Count distinct in Set Analysis

Anonymous — Mon, 02 Jul 2018 16:23:14 GMT

HI Anil;

So thinking i did something wrong I attempted these steps.

Did a basic count statement:

=count(DISTINCT([CHK_NO]))

this was successful as i got an accurate count of total checks and well as when I selected one.

It is now when i want to separate them by amount category, my thought was i need to sum the [PAID_AMT] and then put it in a range.

This is the portion I am not sure how to do.

Re: Count distinct in Set Analysis

Anil_Babu_Samineni — Tue, 03 Jul 2018 01:29:21 GMT

In case, if you see my reply there is second approach for objects. You can try that way as well..