Count number of distinct TVLicense numbers that are used more than once

[#1]

hi there.

Lets say I have a table showing TV license sale transactions.  I have 200 transactions and 20 distinct (unique) TV licenses, some of which were used more than once.

I've created a straight table that shows only TV licenses that were used more than 10 times in a given period.

TV License number	No. of times used	Count distinct
3658606857	50	1
0967560945	37	1
5867698505	28	1
5857676859	15	1
4957676954	11	1
		20

How do I make the "Count distinct" total show a total of 5 instead of 20? I.e. I only want to know how many unique licenses were used more than 10 times, NOT total number of unique licenses.

Hope you can help!

[Chanty4u]

use rowno()

[swuehl]

Try something like

=Count({<License = {"=Count(License)>10"}>} DISTINCT License)

[HirisH_V7]

Hi,

If Your data is like this,

LOAD * INLINE [
    TV License number, No. of times used
    3658606857, 50
    0967560945, 37
    5867698505, 28
    5857676859, 15
    4957676954, 11
    4323223232, 09

];

Then in table Expression,

IF([No. of times used]>10,Count(DISTINCT [TV License number]))

Hope this Helps,

PFA,

Hirish

[HirisH_V7]

Hi,

Try this,

IF([No. of times used]>10,Count(DISTINCT [TV License number])) 

[#6]

Hi Hirish

I tried this but im not sure why it didn't work?

[#7]

Thanks Swuehl

This worked perfectly.

[oknotsen]

If that means your question is now answered, please flag the Correct Answer.

If not, please make clear what part of your question still needs answering.

[Peter_Cammaert]

Because in your situation (License not being a dimension)  evaluating field [No of times used] outside of an aggregation function results in a NULL value because it delivers a large number of different values.

Moreover, you don't want to walk this path, as moving the IF inside the count() call - which would solve your issue - is very bad for performance. Set analysis is way better...

[HirisH_V7]

Hi,

Can you post your Sample.

Hirish