Trying to Count the number of Appointments in my data

Cwal
Cwal Member
edited October 2022 in Charting

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 #`))

Screenshot 2022-10-28 104330.jpg

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.

Tagged:

Best Answers

  • Billobi
    Billobi Contributor
    Answer ✓

    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
    mhouston Contributor
    edited October 2022 Answer ✓

    @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)

Answers

  • Cwal
    Cwal Member
    edited October 2022


  • Billobi
    Billobi Contributor
    Answer ✓

    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
    Cwal Member

    @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:

    image.png

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

    image.png

    when I remove

    not like 'No Code0No Bay' 

    leaving in

    not like 'No Code0BAY 0'

    in it's place, the data looks accurate:

    image.png

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

  • mhouston
    mhouston Contributor
    edited October 2022 Answer ✓

    @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
    Cwal Member

    @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!

Categories