How to make a chart which shows weekly values along with percentage change?

hamza_123 · February 2020

I want to make a chart which shows the weekly value of my sales, along with the % change every week. This % change can be in the form of a bar, a long, or a symbol (along with % value).

Anyone knows how to do this? and whats the best way to do this?

GrantSmith · February 2020

@hamza_123

I've done something in the past where I calculate the current day value and the value from a year ago. The data set is aggregated on a daily basis (to allow slicing on day / week / month / year) and I simply use a MySQL ETL to join the dataset to itself shifting the current date by a 1 year interval (or 364 days if you want to compare same day of the week this year to last year i.e. Monday -> Monday), renaming the columns to include LY for Last Year. Then in my cards I'll use a BeastMode compute the YOY % difference

(SUM(`This Year Field`) - SUM(`Last Year Field`)) / SUM(`Last Year Field`)

Utilizing a Line + Stack Bar chart type (Under Verital bar chart type) I'll put in all the YOY % values for the different groups as the lines (You can specify the number of series to include as lines under General -> Series on Left Scale) and then put in the current day / year's values as the bars.

It ends up being a bit wieldy but it allows me to calculate the current month YOY % correctly (ignoring the same date from last year beyond today - for example if it was 3/5/20 it'd ignore 3/6/19-3/31/19) without having to utilize YTD. It also allows me to do things like plotting a stacked bar with YOY % as lines.

MarkSnodgrass · February 2020

I would suggest the Period over Period cards. You could use the bar line, for example. You can then use the date range filter to compare it to the previous week, or much earlier, if needed. Here is a screenshot example:

CORRECTION: The variance bar line would be a better choice as it shows the percent change. Apologize my screenshot is with the bar line and not the variance bar line.

hamza_123 · February 2020

Thanks for the reply.

Can I use a stacked bar and then compare it WoW or YoY?

MarkSnodgrass · February 2020

It doesn't look like stacked bar is an option in the period over period charts. There are about 7 different charts for period over period. I would try each of them out and see which one fits your need the best.

GrantSmith · February 2020

@hamza_123

I've done something in the past where I calculate the current day value and the value from a year ago. The data set is aggregated on a daily basis (to allow slicing on day / week / month / year) and I simply use a MySQL ETL to join the dataset to itself shifting the current date by a 1 year interval (or 364 days if you want to compare same day of the week this year to last year i.e. Monday -> Monday), renaming the columns to include LY for Last Year. Then in my cards I'll use a BeastMode compute the YOY % difference

(SUM(`This Year Field`) - SUM(`Last Year Field`)) / SUM(`Last Year Field`)

Utilizing a Line + Stack Bar chart type (Under Verital bar chart type) I'll put in all the YOY % values for the different groups as the lines (You can specify the number of series to include as lines under General -> Series on Left Scale) and then put in the current day / year's values as the bars.

It ends up being a bit wieldy but it allows me to calculate the current month YOY % correctly (ignoring the same date from last year beyond today - for example if it was 3/5/20 it'd ignore 3/6/19-3/31/19) without having to utilize YTD. It also allows me to do things like plotting a stacked bar with YOY % as lines.

How to make a chart which shows weekly values along with percentage change?

Best Answer

Answers

Categories