How can I group these results by date?

Zel
Zel Member
in Cards, Dashboards, Stories

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]

image.png

Best Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @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
    RobSomers Coach
    Answer ✓

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

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

  • MichelleH
    MichelleH Coach
    Answer ✓

    @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
    ArborRose Coach
    Answer ✓

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

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @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
    Zel Member
    edited March 2023

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

    [IMG1]

    image.png

    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]

    image.png

  • ArborRose
    ArborRose Coach
    edited March 2023

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

    image.png

    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.

    image.png

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

    image.png

    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.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • Zel
    Zel Member

    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
    RobSomers Coach
    Answer ✓

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

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

  • MichelleH
    MichelleH Coach
    Answer ✓

    @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
    ArborRose Coach
    Answer ✓

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

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • Zel
    Zel Member

    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!

Categories