Count distinct in Set Analysis

[Anonymous]

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

[kfoudhaily]

first one:

Count({$<

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

     [PAID_AMT]={"<=2500"}

     >}

      distinct([PAID_AMT])

         )

[Anil_Babu_Samineni]

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))))

Or

=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))

[Anonymous]

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

[Anonymous]

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?

[Anil_Babu_Samineni]

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??

[Anonymous]

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.

[Anil_Babu_Samineni]

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