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

hamza_123

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

@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

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

Thanks for the reply. 

 

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

MarkSnodgrass

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

@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.