hello , how to compare month to month with secondary axis in card visualization?
Best Answer
-
Hi, user04753,
If I understand your question correctly, you want a card that has a month-over-month comparison of two sets of metrics (count; avg interactions) that will appear on two different y axis scales, broken out by Type on the x axis. Is that correct?
I think you can do this using a Beast Mode. Check out this Knowledgebase article that explains some general concepts and give examples: http://knowledge.domo.com?cid=beastmodecomparison
Since you effectively want to include current and prior month count and average number of interactions (a total of 4 metrics), you'll need to create each of those metrics as a separate beast mode. That's because of the way the "Line + Grouped Bar" chart works.
The logic in these beast modes is to count or average the interactions if the month of the date is the same as the month of the current date (current month) or if the month of date + 1 month is the same as the month of the current date (prior month).
The four beast modes:
1) Current Month Count:
count(
case
when month(`Date`) = month(CURRENT_DATE()) then `Type`
end
)2) Prior Month Count:
count(
case
when month(date_add(`Date`,interval 1 month)) = month(CURRENT_DATE()) then `Type`
end
)3) Current Month Interactions:
avg(
case
when month(`Date`) = month(CURRENT_DATE()) then `Number of Interactions`
end
)4) Prior Month Interactions:
avg(
case
when month(date_add(`Date`,interval 1 month)) = month(CURRENT_DATE()) then `Number of Interactions`
end
)Create the visualization:
To create the visualization, you'll put "Type" on the X axis, then add "Prior Month Count" as the Y axis, followed by "Current Month Count", "Prior Month Interactions", and "Current Month Interactions" as series.
By default, this chart will plot the field in the Y axis as a line and will plot each of the three series as bars. You can change that defualt behavior by changing this parameter: Chart Properties --> General --> Series on Left Scale. By entering the value 2, you're telling Domo to plot the first two fields as lines and all remaining fields as bars.
Your chart will look something like this:2
Answers
-
I think that you would need to make these changes in SQL or an ETL.
Basically, you would need to add a field to your data set for `Previous Month Date` this would just be
SELECT
*,
DATESUB(`Date_Field`, INTERVAL 1 MONTH) as `Previous Month Date`
FROM data_table
You would the new table with the original table
SELECT
a.*,
b.`type` as `Previous Month Type`,
b.`number interactions` as `Previous Month number interactions`
from new_table a
left join data_table b
on a.`unique ID`=b.`Unique ID` AND a.`Previous Month Date`=b.`Date`
This should give you a table that includes the current months value and the previous months value in the same line.
Then you would add the two new fields to your visualization: type, previous month type, number interactions, previous month number interactions.
Set the left axis to include 2 fields and you should be all set. Let me know how this goes. Good luck
1 -
Hi, user04753,
If I understand your question correctly, you want a card that has a month-over-month comparison of two sets of metrics (count; avg interactions) that will appear on two different y axis scales, broken out by Type on the x axis. Is that correct?
I think you can do this using a Beast Mode. Check out this Knowledgebase article that explains some general concepts and give examples: http://knowledge.domo.com?cid=beastmodecomparison
Since you effectively want to include current and prior month count and average number of interactions (a total of 4 metrics), you'll need to create each of those metrics as a separate beast mode. That's because of the way the "Line + Grouped Bar" chart works.
The logic in these beast modes is to count or average the interactions if the month of the date is the same as the month of the current date (current month) or if the month of date + 1 month is the same as the month of the current date (prior month).
The four beast modes:
1) Current Month Count:
count(
case
when month(`Date`) = month(CURRENT_DATE()) then `Type`
end
)2) Prior Month Count:
count(
case
when month(date_add(`Date`,interval 1 month)) = month(CURRENT_DATE()) then `Type`
end
)3) Current Month Interactions:
avg(
case
when month(`Date`) = month(CURRENT_DATE()) then `Number of Interactions`
end
)4) Prior Month Interactions:
avg(
case
when month(date_add(`Date`,interval 1 month)) = month(CURRENT_DATE()) then `Number of Interactions`
end
)Create the visualization:
To create the visualization, you'll put "Type" on the X axis, then add "Prior Month Count" as the Y axis, followed by "Current Month Count", "Prior Month Interactions", and "Current Month Interactions" as series.
By default, this chart will plot the field in the Y axis as a line and will plot each of the three series as bars. You can change that defualt behavior by changing this parameter: Chart Properties --> General --> Series on Left Scale. By entering the value 2, you're telling Domo to plot the first two fields as lines and all remaining fields as bars.
Your chart will look something like this:2 -
UPDATE: did start a new thead as well since this may not be seen by others who might've been looking to find a solution to something similar. Thanks!
I was going to start a new thread but this sounds like it might be along the lines of what we're looking to do but in a table, so wondering if either of these solutions (beast mode or SQL/ETL) can accommodate MORE than 2 dates in a single row of data to compare several months, showing total discrepanices along with the percentage difference from the previous month. The month over month bar chart doesn't seem to be able to visualize what we need, and hoped maybe it can in a table.
EXAMPLE:
We want to show, from left to right, CURRENT MONTH (December) data w/PRIOR MONTH (November) percentage variance, then November compared to October, & October compared to September. The column to the left would be by company/organization along w/current month, # of discrepanices, & the % change over the previous month. Then it would continue, left to right, for the previous 3 months from the current month.
Can this be done?
0 -
Since this thread was already considered solved, let me address your other thread (https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Table-compare-several-month-over-month/m-p/38216#M6274)
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive