topic Expression Definition in QlikView

Expression Definition

Sun, 30 Oct 2011 14:31:02 GMT

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 — Sun, 30 Oct 2011 14:50:01 GMT

I think it should look like

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

instead, right?

Sun, 30 Oct 2011 14:58:48 GMT

Thanks swuehi, your inputs helped to solve.

Sun, 30 Oct 2011 15:01:52 GMT

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

swuehl — Sun, 30 Oct 2011 15:07:42 GMT

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

Sun, 30 Oct 2011 15:14:19 GMT

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

swuehl — Sun, 30 Oct 2011 16:16:55 GMT

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.