Dashboard: How to show the tendency of a chart?

Karl Abran · 03/28/21

Hello everyone!

I hope my question is clear, English is a second language for me. I was wondering if any of you have tips as to the easiest way to indicate the tendency (like the stock market per exemple) on a chart on a dashboard? Is it possible at all or is there a widget or a way to do a calculation in a sheet that could lead me to the same result? If possible, I would like to include the red or green arrow depending on the loss or gain on the chart.

P.S - I only took GameStop stock for the sake of the example, I'm not in finance at all.

Thank you all for your help, this community is really great!

Karl

Paul Newcome · 03/31/21

We can use formulas to extract your trend, and we can store them in Sheet Summary Fields. You can have the fields set up as symbol type columns (or how ever you want them to be displayed).

The actual formulas are going to depend on the exact comparisons you want to show. Are you wanting to compare the past 30 days to the 30 days before that? If so, are you looking for a sum or an average? Are you wanting to show today's as compared to yesterday's? Basically... Exactly what are you comparing to what to show the trend?

Paul Newcome · 04/01/21

Ok. Is this per floor or across all floors? It looks like all of your charts in your screenshot have all floors.

The basics for the formula would be to first pull the last 30 days.

Formula A:

=SUMIFS({Scores}; {Dates}; @cell>= TODAY(-30))

Then pull 30 days past from yesterday.

Formula B:

=SUMIFS({Scores}; {Dates}, AND(@cell >= TODAY(-31), @cell < TODAY()))

Of course if you were wanting to break it down by floor or include any other variables, you'll need to include those range/criteria sets in both of the SUMIFS, but this at least shows how to work the dates.

Then you would drop these into a nested IF formula to output the desired symbol or text (whatever you choose for the sheet summary fields).

=IF(Formula A < Formula B, "Down", IF(Formula A = Formula B, "Unchanged", "Up"))

=IF(SUMIFS({Scores}; {Dates}; @cell>= TODAY(-30)) < SUMIFS({Scores}; {Dates}, AND(@cell >= TODAY(-31), @cell < TODAY())), "Down", IF(SUMIFS({Scores}; {Dates}; @cell>= TODAY(-30)) = SUMIFS({Scores}; {Dates}, AND(@cell >= TODAY(-31), @cell < TODAY())), "Unchanged", "Up"))

Paul Newcome · 03/29/21

You would need to have a dedicated cell where you compares the data from TODAY() to the data from TODAY(-1) in a nested IF statement.

Are you able to provide a screenshot of exactly how your data is currently laid out?

Karl Abran · 03/29/21

Hi Paul,

Thank you for your time. This is still a prototype, and its in french so just ask if you need me to translate something.

We have a form that collects the observations of employees and according to the answer choices of the different categories, a score is assigned to the subject. Here's a little sample:

Based on the data collected, we have another sheet that calculates the average by category per floor (I work for a hospital) based on the last 30 days.

And subsequently the results are shown in a dashboard. However, we would like to be able to make the tendency visible.

Paul Newcome · 03/29/21

Exactly what are you trying to show the trend of? I see in your formula you are using TODAY(-30). Do you have any other dates in your metrics? Are you currently capturing historical data (the numbers from yesterday) as static data somewhere?

Karl Abran · 03/29/21

Yes I am capturing historical data. Each time a form is filled out, the creation date is recorded as shown in the screenshot below. The form is filled in every day on each floor. I am looking to have the daily trend and the monthly trend so that we can get a good idea of if we are improving or if the evaluated subject is getting worse by the day.

I guess I'm going to have to compare TODAY (-30) with YESTERDAY (-30) and LASTMONTH (-30), I'm just not sure how to do this effectively. Do I have to make myself another spreadsheet and do these calculations independently? Or if there isn't a function to do it quickly?

Paul Newcome · 03/31/21

Do you have access to Sheet Summary Fields?

Karl Abran · 03/31/21

@Paul Newcome I do, I never used it but i'll go watch all the tutorial I can find. I'll be able to extract the trend of my data over the last month?

Paul Newcome · 03/31/21

We can use formulas to extract your trend, and we can store them in Sheet Summary Fields. You can have the fields set up as symbol type columns (or how ever you want them to be displayed).

The actual formulas are going to depend on the exact comparisons you want to show. Are you wanting to compare the past 30 days to the 30 days before that? If so, are you looking for a sum or an average? Are you wanting to show today's as compared to yesterday's? Basically... Exactly what are you comparing to what to show the trend?

Karl Abran · 03/31/21

If we can do that it would be great! I'll have to go see some tutorials on how it works (the sheet summary fields). Thank you for putting me on a solution path.

I compare the score by category of the last 30 days with the previous month and also the daily fluctuation, so the score of the last 30 days of today compared to the last 30 days of yesterday.

Paul Newcome · 04/01/21