Beast mode to count number of occurrences within a time period

user16839
user16839 Member
in Magic ETL

I'm new to Domo. I have a business request to count the number of instances when an event occurred more than once in a given date filter. Example: Event X happened 5 times in the selected date range, Event Y happened 4 times in the date range, and Event Z happened 1 time in the selected date range. The count for the date range should show 2 since X & Y happened more than once in the selected date range. 

 

I created the following beast mode to calculate the number of times an event occurred in the date range. I set the beast mode value to 1 if the event occurred more than once during the time period. My beast mode appears to be working as I see my beast mode set as 1 for Event X & Y but 0 for Event Z.

 

 

(CASE When

 

(Count(`Event`))

 

> 1 then

 

1

 

ELSE NULLIF(0,0)

 

END)

 

I was thinking I could then sum up the aforementioned beast mode within a new beast mode to show 2 as the measure, but unfortunately I have not been able to get the second beast mode to work. Any recommendations to get summarized calculation on a beast mode to work? Please let me know if you need any additional information.

Best Answer

  • Ashleigh
    Ashleigh Coach
    Answer ✓

    Okay, try adding the event name or type in the series column. This will basically allow the graph to count the number of events that appear multiple times so the y axis should have the 15. It would show the number of times something occurred which I know you do not want but it would only show if you hover over it.

    This is my beast mode: case when COUNT(`Event`) > 1 then 1 else 0 end

    This is what the graph looks like. I am using the same data I showed you before but am graphing it by week. So from 1/1/19-1/5/19 the occurrences was 2 for x, 2 for y, and 1 for z so the bar for that week shows 2. And then for the following week the occurrences were 1 for each so it shows 0.

    That’s about the closest you will get I think.

    **If this answer solved your problem be sure to like it and accept it as a solution!

Answers

  • Ashleigh
    Ashleigh Coach

    @user16839  Does this need to be done in a beastmode? It might be easier to use an ETL. You could use grouping and count the number of times something occurs. You could also use a different card type that would show how many occurances there was in the date range. 

    **If this answer solved your problem be sure to like it and accept it as a solution!

  • user16839
    user16839 Member
  • user16839
    user16839 Member

    Hi:

     

    I tried creating an ETL but it only works when selecting all time as the date range. I added my ETL in the previous post. The ETL processes all data and determines if there are more than one occurance of an event. If yes, the event is summarized and then available in the output. As an example, event Z happened in 2016 and 2019 which means its a multiple event all time. When changing the date range to 2019, event Z still shows up as a multiple event even though the 2016 event should not be included due to the date range.

     

    I thought a beast mode would be dynamic that would recalculate each time a user changes the filters. The ETL only runs when the input data changes. Maybe I am missing something? 

  • Ashleigh
    Ashleigh Coach

    @user16839  Correct, the calculation would only be a onetime calculation in the ETL, you would have to manually apply the date filter in the beginning. I understand why a beast mode might be more useful.

    How are you presenting the data? Is it a table or some other chart? You could use the beast mode you have that counts occurrences and then use a calendar chart or some other chart that would graph those thus showing the sum. You could use a bar chart and graph it by event using the beast mode you have which should ultimately give you a list of each event with its number of occurrences.

    **If this answer solved your problem be sure to like it and accept it as a solution!

  • user16839
    user16839 Member


    I'm trying to use a bar chart to display the data. I think summing my beast mode would make the calculation work, but I can't get the syntax to work. Here is my attempt at the beast mode. Can you advise how to get the summary count based on a beast mode to work?

     

    Sum

    (

    -- Start Beast mode comment

    (CASE When

    (Count(`Event`))

    > 1 then

    1

    else NULLIF(0,0)

    END)

    --End beast mode comment

    )

  • Ashleigh
    Ashleigh Coach

    @user16839  how are you determining the date range? Are you just filtering the data or are you showing each date range in the card? 

     

    I am not 100% sure on what you are asking for without being able to see the data so I made some , does it have the right concept?. Here is some photos of what I made, is this something you are looking for or are you looking for something different?

     

    Date                                       Event

    1/1/2019                                x

    1/2/2019                                y

    1/3/2019                                z

    1/4/2019                                x

    1/5/2019                                y

    1/9/2019                                x

    1/10/2019                              y

    1/11/2019                              z

    **If this answer solved your problem be sure to like it and accept it as a solution!

  • user16839
    user16839 Member

    Date   Event
    1/1/2019  x
    1/4/2019 x
    1/9/2019 x
    1/2/2019 y
    1/5/2019 y
    1/10/2019 y
    12/30/2018 z
    1/11/2019 z

     

    I modified your sample data to set one of the Z records to 2018. In the following scenario, a date filter of All Time would summarize the beast mode as 3 since events X, Y, and Z each happened multiple times during the time frame. Changing the date filter to 2019 would summarize the beast mode as 2 since only X & Y each happened multiple times in the timeframe. 

    Does that help?

     

  • Ashleigh
    Ashleigh Coach

    In the photo I provided I added the date filter for 1/1/2019-1/5/2019 so it would exclude the last 3 from my orginal dataset. So the photo showed 2 events happened twice and 1 event happend once. 

    **If this answer solved your problem be sure to like it and accept it as a solution!

  • user16839
    user16839 Member

    Unfortunately I wasn't able to view the screenshot. I received an error. 

  • Ashleigh
    Ashleigh Coach

    Try downloading it and then looking, that is usally what I have to do. 

    **If this answer solved your problem be sure to like it and accept it as a solution!

  • user16839
    user16839 Member

    But it sounds like you understand the request. The only modification from your reply is that we don't want to see the events that happened only one time

  • Ashleigh
    Ashleigh Coach

    hmm filtering those out would be the hard part. At least with the bar graph you could sort it to where single ones would only be at the bottom. 

    **If this answer solved your problem be sure to like it and accept it as a solution!

  • Ashleigh
    Ashleigh Coach

    I was able to make this beast mode which counts the counts basically. Only thing is you can not filter out the single ones because when you try to filter it they all add up. I am working on that now. 

     

    case when COUNT(`Event`) > 1 then COUNT(`Event`) else 0 end

    **If this answer solved your problem be sure to like it and accept it as a solution!

  • user16839
    user16839 Member

    We want to show the measure as 2 on the graph, just for X & Y. We don't want to show how many times each multiple event occurred. Just that events X & Y each happened more than once.

     

    (CASE When

    (Count(`Event`))

    > 1 then

    1

    else NULLIF(0,0)

    END)

     

    My original beast mode sets the measure to 1 if an event happens more than once but it shows only 1 when I add the beast mode to the graph. Summing the beast mode should show 2, which is the desired result. 

     

     

  • Ashleigh
    Ashleigh Coach

    So just one bar with a count of multiple entries? How are you looking to see the data? I can see this becoming just one very long bar if you have alot of data with multiple entries. 

    **If this answer solved your problem be sure to like it and accept it as a solution!

  • user16839
    user16839 Member

    See screenshot attachment for desired graph. We are counting the number of events that occurred multiple times in a given period. Look at sample data for Dec-18. There are 15 instances of specific events that occurred multiple times in Dec 2018. My Best Mode assigns a 1 in the data table column for these multiple events or 0 for events that occurred only once. When I put the beast mode into the bar chart value column, the measure shows 1 not the summarized 15. That's why I want to summarize my beast mode so that I see 15 not 1.

  • Ashleigh
    Ashleigh Coach
    Answer ✓

    Okay, try adding the event name or type in the series column. This will basically allow the graph to count the number of events that appear multiple times so the y axis should have the 15. It would show the number of times something occurred which I know you do not want but it would only show if you hover over it.

    This is my beast mode: case when COUNT(`Event`) > 1 then 1 else 0 end

    This is what the graph looks like. I am using the same data I showed you before but am graphing it by week. So from 1/1/19-1/5/19 the occurrences was 2 for x, 2 for y, and 1 for z so the bar for that week shows 2. And then for the following week the occurrences were 1 for each so it shows 0.

    That’s about the closest you will get I think.

    **If this answer solved your problem be sure to like it and accept it as a solution!

Categories