Trying to Count the number of Appointments in my data

Cwal

I feel like this should be simple but I'm struggling with how to write the beast mode to where I get a whole number and not an average.

I'm trying to count the total number of appointments in my sales data. This is what I've written:

Count(`Bay Text`)/(COUNT(DISTINCT`Invoice #`))

I've attached a photo showing what the data looks like. Each block outlined in color marks a different invoice (see the Invoice # column). Our data brings over rows of data for each line item on an invoice. Each line item shows the appointment type (Appt Text), bay code (Bay Code), and bay name (Bay Text) for each invoice (this is copied for each line item on an invoice even though its one entry of data for each invoice).

Invoices marked 'No Code' in Appt Text, '0' in Bay Code, and 'No Bay' in Bay Text indicate that that invoice did not have an appointment.

Any help/guidance is appreciated.

ps: I should clarify that I am trying to do this at the Beast Mode level and not the ETL level due to the fact that this data set is used across 1000s of cards and removing duplicates with a group by tile would effect many other cards and I'm not trying to create a separate output dataset for this one card.

Billobi

So if "Invoices marked 'No Code' in Appt Text, '0' in Bay Code, and 'No Bay' in Bay Text indicate that that invoice did not have an appointment", does that mean that all other invoices have an appointment? If so I'd just do an evaluation against those specific contents to give you a unique count of the 'Invoice #' field.

COUNT(DISTINCT CASE WHEN CONCAT('Appt Text','Bay Code','Bay Text') NOT LIKE 'No Code0No Bay' then 'Invoice #' end)

(you could customize that concat however you like or add combinations to exclude other attributes that would indicate no appointment)

mhouston

@Cwal it looks like the problem is in the SQL syntax. I think I understand you are trying to exclude both values 'No Code0Bay 0' and  'No Code0NoBay'? So your SQL should be AND, not OR.

Also a side question - why are you using the like operator without any wild cards? I think you could just use an equal.

I'd rewrite your beast mode:

COUNT(DISTINCT

CASE WHEN concat(`Appt Text`, `Bay Code`, `Bay Text`)

<> 'No Code0NoBay' and concat(`Appt Text`, `Bay Code`, `Bay Text`) <> 'No Code0Bay 0' then `Invoice #`

end)

Cwal

Billobi

So if "Invoices marked 'No Code' in Appt Text, '0' in Bay Code, and 'No Bay' in Bay Text indicate that that invoice did not have an appointment", does that mean that all other invoices have an appointment? If so I'd just do an evaluation against those specific contents to give you a unique count of the 'Invoice #' field.

COUNT(DISTINCT CASE WHEN CONCAT('Appt Text','Bay Code','Bay Text') NOT LIKE 'No Code0No Bay' then 'Invoice #' end)

(you could customize that concat however you like or add combinations to exclude other attributes that would indicate no appointment)

Cwal

@Billobi

This does work but by using this, I actually found that one of our locations has their bay text defaulted to 'BAY 0' instead of 'No Bay' like the other locations.

so I added this line to the beastmode:

But this returns data for this one store in question that looks like this:

when I remove

not like 'No Code0No Bay' 

leaving in

not like 'No Code0BAY 0'

in it's place, the data looks accurate:

Any idea why? Or what I'm doing wrong? Thanks for the help!

mhouston

@Cwal it looks like the problem is in the SQL syntax. I think I understand you are trying to exclude both values 'No Code0Bay 0' and  'No Code0NoBay'? So your SQL should be AND, not OR.

Also a side question - why are you using the like operator without any wild cards? I think you could just use an equal.

I'd rewrite your beast mode:

COUNT(DISTINCT

CASE WHEN concat(`Appt Text`, `Bay Code`, `Bay Text`)

<> 'No Code0NoBay' and concat(`Appt Text`, `Bay Code`, `Bay Text`) <> 'No Code0Bay 0' then `Invoice #`

end)

Cwal

@mhouston Bingo. Using AND not OR, and using an equal instead of like both fixed the issue and corrected the count. Thank you both @Billobi and @mhouston for helping!