Dashboard: How to show the tendency of a chart?

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!



Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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"))