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.
> 1 then
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.
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.
@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.
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?
@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.
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?
-- Start Beast mode comment
> 1 then
--End beast mode comment
@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?
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?
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.