Expression Definition

[#1]

I have a table which contains data as follows

Country,  User ID,  CardUsageCount , keyUsageCount, MMYYYY

USA,      U12,         122,                    221                  , 042011

USA,      U16,          0,                      500                  , 042011

USA        U12,         60,                    122,                 ,052011

USA       U60,           200,                 120,                 , 052011

CANADA, C43,        22,                    340,                 ,042011

I want to create a barchart  of CardUsers per country.

So the filnal data will look like as follows

Country, CardUageCount

USA,       2 

CANADA  1

(One user U16 will be dropped as CardUsageCount=0. Since it is Distinct, U12 will be counted only once)

I kept Dimension = Country

Expression = if(  (Count ( CardUsageCount)) >= 1, Count (DISTINCT CardUsageCount))

I am not getting the results I needed. Anything wrong with my expression?

Thanks

[swuehl]

I think it should look like

=Count (DISTINCT if(CardUsageCount>0, [User ID]))

instead, right?

[#3]

Thanks swuehi, your inputs helped to solve.

[#4]

In case if I need to add %of CardUsers percountry, how do I do it?

[swuehl]

Could you give an example by using your above sample data how do you define % of CardUsers per Country?

[#6]

I have large data about 5000 lines. To simplify I have provided few lines of data in the above example.

[swuehl · Accepted solution]

Yes, I intended to know what your definition of this percentage is.

I could think of e.g.

=Count (DISTINCT if(CardUsageCount>0, [User ID])) / count( if(CardUsageCount>0, [User ID]))

so it will give e.g. for USA a 66% percentage, or maybe

=Count (DISTINCT if(CardUsageCount>0, [User ID])) / count([User ID])

giving a 50% percentage or

=Count (DISTINCT if(CardUsageCount>0, [User ID])) / count (distinct [User ID])

giving again 66% in this sample.

Since I don't know what you define as % of CardUsers per Country, I am just guessing.