How can I group these results by date?

Zel

Hello, I have to get the total unique alarm count per date, can someone help me, please?

The output should look like below (see [IMG1]).

I have two slicers:

1. Healthcare
2. Month

What I wanted to do is: I want to count the total unique alarms per date, depending on which was selected in the slicer. The first two columns are good.

I just don't know how to group the alarm counts by date showing the same result as below.

I tried using pivot table and use the code below for the unique alarm ID:

COUNT(DISTINCT `Alarm ID`)

Please help me how to group it by date, thank you.

[IMG1]

MichelleH

@Zel Do you have a date field in your dataset or just month? If you already have the date then you can create a Pivot Table card with Healthcare and Date in the columns and Unique Alarm ID in the values. Then you can sort low to high by Date

RobSomers

@Zel Is your date column just dates or is it datetime? If it's datetime it could look like the same date but they're actually unique because they have different times in the background. If it is a datetime column, try using a DATE() function on it and see if that works.

MichelleH

@Zel I agree with @RobSomers that your dates are likely getting duplicated because they are datetime rater than date. Forcing them to a date should help.

ArborRose

@Zel. You don't need to force your current column to date. If your data is in datetime, create a new field for the report using date() but keep your original so it is maintained for your recordkeeping.

MichelleH

@Zel Do you have a date field in your dataset or just month? If you already have the date then you can create a Pivot Table card with Healthcare and Date in the columns and Unique Alarm ID in the values. Then you can sort low to high by Date

Zel

Hi @MichelleH, thank you for looking into this. I tried to use Pivot Table and here is the arrangement:

[IMG1]

Rows: Date

Columns: healthcare name

Values: COUNT(DISTINCT `Alarm ID`)

See [IMG2] for the output

If possible, I'd like to retain the dates in the rows like what was shown in excel, since it was particularly requested by the user. Then like for example: 2/2/2023 (it has 2 counts of unique ID per image below), I'd like to merge that into just one date and show 2 as the result. I'm having trouble grouping it by date. 😅

2/2/2023 == 2

2/5/2023 == 1

2/8/2023 == 3

etc…

[IMG2]

ArborRose

There is no need for pivot tables. Assume you have a webform (in your case the incoming dataset gathered from the alarm system software).

You would just put those fields on the card as healthcare name, alarm received, and count(alarm received). Then add sorting and format your labels.

And the final result would be a list by healtcare facility, date, and the count per date.

You can add a slicer by creating a filter on the same dataset and adding it to the same dashboard. Or use a variable slicer.

Zel

Hi @ArborRose , when I tried the simple table card, I'm still getting almost same result with what I have on the pivot for the alarm ID.

I only need to get the count of unique alarm IDs which is grouped by date.

Note: Some alarm IDs have multiple transactions, so I have to use distinct function. Can you help me with this one please?

RobSomers

@Zel Is your date column just dates or is it datetime? If it's datetime it could look like the same date but they're actually unique because they have different times in the background. If it is a datetime column, try using a DATE() function on it and see if that works.

MichelleH

@Zel I agree with @RobSomers that your dates are likely getting duplicated because they are datetime rater than date. Forcing them to a date should help.

ArborRose

@Zel. You don't need to force your current column to date. If your data is in datetime, create a new field for the report using date() but keep your original so it is maintained for your recordkeeping.

Zel

Hello @MichelleH @RobSomers @ArborRose, thank you so much for all of your inputs. It's working now. I just used the date() function and it worked perfectly!