COUNT DISTINCT across several fields

[#1]

I know how to count distinct values for one field. But how do I count distinct combinations of fields for each row of data.

Is this anywhere close to be correct?

COUNT (DISTINCT Activities.Subject & Activity.CalendarDate & Activities.Type & Activities.Description)

I tried this and it seems to work, however for a couple of months this produces more results than for the individually counted rows! I'm obviously doing something wrong as this should be impossible.

[#2]

The way I would approach this:

Get all the fields required into one table on the backend and then use autonumberhash to create a key out of those fields.

Autonumberhash128(Activities.Subject,Activity.CalendarDate,Activities.Type,Activities.Description) as KeyField

Then on the front end you can use count(Distinct KeyField)

Thanks

AJ

[maxgro]

I prefer to add add a separator between fields 

COUNT (DISTINCT Activities.Subject & '-' & Activity.CalendarDate & '-' & Activities.Type & '-' & Activities.Description)

As Ayaj suggests is better to do at the backend, just another way is

Activities.Subject & '-' & Activity.CalendarDate & '-' & Activities.Type & '-' & Activities.Description  as KeyField

In my experience there is some (sometimes big) improvement in chart time calculation when you change

from

COUNT (DISTINCT Activities.Subject & '-' & Activity.CalendarDate & '-' & Activities.Type & '-' & Activities.Description)

to

count(Distinct KeyField)

[Clever_Anjos]

Are your fields from same table?

If yes, please use Ajay or Massimo suggestions.

If they´re coming from more than one table, you have to use some

LOAD distinct

Activities.Subject

Resident T1

....

to calculate what you want

[#5]

Thanks Massimo, I will try you suggestions of using the separator. As this is simpler for me

How can I ensure that rows with no data are ignored in the count? I have a row for each date, regardless if there is an activity on it, but the blank rows are being counted

[#6]

hi

try this

load

Activities.Subject & Activity.CalendarDate & Activities.Type & Activities.Description as key

from table;

then try this

count(Distinct key)