HELP CENTER

HELP CENTER

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Dojo
- :
- Using Domo
- :
- Card Building
- :
- show percent on period over period graph

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

Tuesday

Tuesday

show percent on period over period graph

I want to show the percentage of anonymous callers on a period over period bar graph but the %percent over time option in the bar label setting uses the total of the entire time periods (current quarter, prior quarter and prior year) in the denominator. I want the percentage of anonymous just for the quarter and one for the prior quarter and one of the prior year bar.

I am thinking i need to create beast mode field(s) to do this but can't seem to get it. This Relates to whether a hotline call was anonymous or not.

Here is what I have: SUM(`Anonymous` when 'yes')/ SUM(`Anonymous` when 'yes' AND 'no')

I have tried using count and count case when but keep getting syntax errors. I have attached a copy of my chart, but the percentages are wrong, as mentioned the denominator is the count of all three time periods. Thank you in advance for any assistance on how to accomplish this. Jill

Highlighted
##

**Was this post helpful? Click the heart icon**

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

Tuesday

Tuesday

Hi @user082291

It's not quite possible to use a period over period chart when you're wanting to plot two different period intervals (quarter and year).

I'm not a real fan of the period over period charts and prefer to do my own as I have more flexibility over the data than I do the chart.

What you can do is create a date dimension table which calculates the dates for this year, last year, last quarter etc. I utilized a MySQL dataflow to calculate these from the calendar dates Domo Dimension dataset.

```
select d.`dt` as "Report Date", date_sub(`dt`, interval '0' year) as "Comparison Date", 'Current' as 'Period Type'
from `calendar_dates` d
UNION
select d.`dt` as "Report Date", date_sub(`dt`, interval '7' day) as "Comparison Date", 'Last Week' as 'Period Type'
from `calendar_dates` d
UNION
select d.`dt` as "Report Date", date_sub(`dt`, interval '1' month) as "Comparison Date", 'Last Month' as 'Period Type'
from `calendar_dates` d
UNION
select d.`dt` as "Report Date", date_sub(`dt`, interval '28' day) as "Comparison Date", '28 Days Ago' as 'Period Type'
from `calendar_dates` d
UNION
select d.`dt` as "Report Date", date_sub(`dt`, interval '364' day) as "Comparison Date", 'Last Year (DOW Aligned)' as 'Period Type'
from `calendar_dates` d
UNION
select d.`dt` as "Report Date", date_sub(`dt`, interval '1' year) as "Comparison Date", 'Last Year' as 'Period Type'
from `calendar_dates` d
UNION
select d.`dt` as "Report Date", date_sub(`dt`, interval '2' year) as "Comparison Date", '2 Years Ago' as 'Period Type'
from `calendar_dates` d
UNION
select d.`dt` as "Report Date", date_sub(`dt`, interval '728' day) as "Comparison Date", '2 Years Ago (DOW Aligned)' as 'Period Type'
from `calendar_dates` d
UNION
select d.`dt` as "Report Date", date_sub(`dt`, interval '3' year) as "Comparison Date", '3 Years Ago' as 'Period Type'
from `calendar_dates` d
UNION
select d.`dt` as "Report Date", date_sub(`dt`, interval '1,092' day) as "Comparison Date", '3 Years Ago (DOW Aligned)' as 'Period Type'
from `calendar_dates` d
UNION
select d.`dt` as "Report Date", date_sub(`dt`, interval '1' quarter) as "Comparison Date", 'Last Quarter' as 'Period Type'
from `calendar_dates` d
```

(feel free to take out scenarios you won't need to reduce the data size of the resulting dataset)

Using this dataset I'll then utilize a Fusion to join my data set date to this date dimension's **Comparison Date** (this is important).

Once I have that Fusion dataset created I can then use that within a card (bar chart in your case) and utilize some beast modes to calculate the numbers like:

Last Quarter:

```
CASE
WHEN SUM(CASE WHEN `Period Type` = 'Last Quarter' THEN 1 ELSE 0 END) = 0 THEN
0
ELSE
SUM(CASE WHEN `Period Type` = 'Last Quarter' AND `Anonymous`='Y' THEN
1
ELSE
0
END)
/
SUM(CASE WHEN `Period Type` = 'Last Quarter' THEN
1
ELSE
0
END)
END
```

Last Year:

```
CASE
WHEN SUM(CASE WHEN `Period Type` = 'Last Year' THEN 1 ELSE 0 END) = 0 THEN
0
ELSE
SUM(CASE WHEN `Period Type` = 'Last Year' AND `Anonymous`='Y' THEN
1
ELSE
0
END)
/
SUM(CASE WHEN `Period Type` = 'Last Year' THEN
1
ELSE
0
END)
END
```

This Year:

```
CASE
WHEN SUM(CASE WHEN `Period Type` = 'Current' THEN 1 ELSE 0 END) = 0 THEN
0
ELSE
SUM(CASE WHEN `Period Type` = 'Current' AND `Anonymous`='Y' THEN
1
ELSE
0
END)
/
SUM(CASE WHEN `Period Type` = 'Current' THEN
1
ELSE
0
END)
END
```

You can then graph by Quarter to get the amounts for This Quarter, Last Quarter, Last Year's Quarter utilizing these beastmodes.

**Was this post helpful? Click the heart icon**

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

Tuesday

Announcements

Click here to submit your story.