Best Of
Re: WE WANT TO HEAR FROM YOU: Where do you go for information for and help with Domo?
Hi Anna, I always start with the knowledge base. :) 9 times out of 10 it answers my question, if I can't find the answer I search the forums to see what the experts have to say.
Re: How do you separate data in a column
You can use the pivot tile to pivot your engagement type values into individual columns. This KB article will be helpful for you.
https://domo-support.domo.com/s/article/360044951294?language=en_US
Re: How do you separate data in a column
Agree with @MarkSnodgrass, pivoting the data is the way to go, and doing it via ETL when you foresee this being needed for other calculations is preferable. If this is only used for this you can go with Beast Modes, so your Score formula would become something like:
0.5*COUNT(CASE WHEN `engagement_type` = 'OUTGOING_EMAIL' THEN `id` END) +1.5*COUNT(CASE WHEN `engagement_type` = 'CALL' THEN `id` END) +COUNT(CASE WHEN `engagement_type` = 'INCOMING_EMAIL' THEN `id` END)
So basically you'll have one entry for each type of engagement that should be accounted for as part of the score.
Re: How do you separate data in a column
@ScottLeeds Is there actually a column called id in your dataset? If not, you can change those portions of the beast mode to a different field, as long as it does not contain nulls.
Re: How do you separate data in a column
As Michelle says, I used Id since it's a column present on most of my tables that I know will never be null, so you can either replace that with a column name that is always provided or if you don't have one, you can update to use the literal number 1 instead of a column name, it'll yield the same result (supposing your dataset is fairly clean). You can also create a separate beastmode for each line without the multiplier to show the number of activities of each kind. I see your formula is using only = 'EMAIL' in the comparison while your sample data contains 'INCOMING_EMAIL' as one of the possible values, so make sure you account for those.
Re: Parameter Filer
Yep, so basically you would join your data 1=1 so that it looks like this:
Then in your card you have your two values:
`data` and `comparison data`
Users select a year and a comparison year and the data for each will be displayed. I would suggest setting default values for each, for example: 2022 vs 2023 and then from there allow users to change the dates.
Re: Parameter Filer
@Anna_Yu This sounds like a good use case for Variables. I'd suggest creating two variables to select the years you want to compare. Here are some sample beast modes you could use with these variables:
Year 1 Total:
sum(case when YEAR(`Date`) = `Year 1` then `Amount` else 0 end)
Year 2 Total:
sum(case when YEAR(`Date`) = `Year 2` then `Amount` else 0 end)
Variance:
sum(case when YEAR(`Date`) = `Year 1` then `Amount` else 0 end) - sum(case when YEAR(`Date`) = `Year 2` then `Amount` else 0 end)
% Variance:
(sum(case when YEAR(`Date`) = `Year 1` then `Amount` else 0 end) - sum(case when YEAR(`Date`) = `Year 2` then `Amount` else 0 end)) / sum(case when YEAR(`Date`) = `Year 2` then `Amount` else 0 end)





