topic Re: Count distinct with set_expression in QlikView

Count distinct with set_expression

Anonymous — Fri, 07 Feb 2014 09:26:17 GMT

Hello,

I need to count the number of occurrences of a name within a week in a year

I tried something like that:

=COUNT( DISTINCT <WeekNumberOfTheYear> Name)

But it doesn't work.

Can anyone help me, please?

Thanks.

Re: Count distinct with set_expression

senpradip007 — Fri, 07 Feb 2014 09:43:36 GMT

try this:

create week number in calendar of your app.

Count({<WeekNo = {'$(=Max(WeekNo))'}, Date=, Day= >} Distinct Name)

Re: Count distinct with set_expression

jonathandienst — Fri, 07 Feb 2014 09:45:34 GMT

Distinct is in the wrong place;

=COUNT(<WeekNumberOfTheYear> DISTINCT Name)

HTH

Jonathan

PS = this is ignoring selections in a field called [WeekNumberOfTheYear]

Re: Count distinct with set_expression

kiranmanoharrode — Fri, 07 Feb 2014 10:10:01 GMT

Dear Edp,

Try below Script

Test:

Laod Date,

Name

from Datasource;

Test1:

Load Week(Date) as WeekNo,

year(Date) as Year,

Count(Distinct Name) as NameCount

resident Test

Group by WeekNo, Year;

Drop table Test;

Regards
Kiran

Re: Count distinct with set_expression

Anonymous — Fri, 07 Feb 2014 10:28:41 GMT

Hi Kiran,

I need the function in the chart, not in the script.

Thank you.

Re: Count distinct with set_expression

Anonymous — Fri, 07 Feb 2014 10:30:40 GMT

Hi Jonathan,

I switched as you explained, but it's doesn't work again (the result is an '-').

Thank you.

Re: Count distinct with set_expression

Anonymous — Fri, 07 Feb 2014 10:32:40 GMT

Hi,

I tried as you explained (I calculated WeekNo As Year*100+Week, so I have 201401, 201402 and so on...), but the result is always 0.

Thank you.

Re: Count distinct with set_expression

MK_QSL — Fri, 07 Feb 2014 10:32:50 GMT

You can create a Straight Table with

WeekNumber as Dimension

and

Count (Distinct Name) as Expression.

Re: Count distinct with set_expression

sundarakumar — Fri, 07 Feb 2014 10:35:01 GMT

Please elaborate your requirement.

What is the dimension u r using? and in what type of chart?

u want to count the number of occurance of a particular name in a partucular week of a year or in all weeks of the year?

Try to post a sample data and expected output.

-Sundar

Re: Count distinct with set_expression

jonathandienst — Fri, 07 Feb 2014 10:39:18 GMT

As we are all just guessing at the problem now, it would be a good idea to post your qvw with some sample data so that can be looked at in more detail.

Jonathan

Re: Count distinct with set_expression

Anonymous — Fri, 07 Feb 2014 11:29:42 GMT

Well, in the attached example, the technician 16997 is "overbooked" in the week 03/02/14, because he's contemporarly busy with the customer 1010000980 and the customer 1010001376.

I'd like to picture the background of the "overbooked" cell with another color, so that they are immediately visible.

Hope it's clear now.

Thank you.

Re: Count distinct with set_expression

Anonymous — Tue, 25 Feb 2014 10:42:00 GMT

Any idea?

Thank you.