1 Reply Latest reply on Apr 19, 2018 1:28 PM by Joe Oppelt

    Dynamic up and down arrows

    Daniel Sheppard

      Hey everyone!

      I am trying to create dynamic arrows, green and pointing up if sales have improved and red and down if sales have gone down. I have pretty much got that to work. It is a pretty simple calculated field dropped on the shape marks card.


      The part that I am having a hard time with getting the number of sales for the previous period. For example, my dashboard has a relative date filter so the user can see the data for last month or last year or whatever they want. I can get the calculated field to compare with last month, but that doesn't help if the user is looking at last years data. The logical comparison would be comparing with the year before that. If i am looking at yesterdays data I would want it to compare to see if sales have gone up or down from the previous day. I hope that makes sense.

      Basically I am having a hard time figuring out how to reference the selection in the data filter in the calculated field so I can be comparing to the right time period.

      Thanks everyone!

        • 1. Re: Dynamic up and down arrows
          Joe Oppelt

          When you filter data on a sheet with a quick filter (including a relative date filter), you eliminate rows from the underlying table that do not satisfy the filter selection criteria.


          But if you use a table calc as a filter, the underlying table still contains the rows that do not fit the table calc filter criteria.  The table calc filter just controls what portion of the underlying table gets displayed on the sheet.


          This leave all the prior data still available to you for looking back.


          Let's assume you are using field [Date] for your relative filter.  Make this calc instead:




          Now use THAT field for your filter instead of [Date].  You can still make a relative filter with it.


          That takes care of your filtering, but how to you get the prior year or the prior month, etc?


          Usually I give the user a "Compare to" parameter that lets him select whether he wants to look back one month or one quarter or one year, etc.


          Then I can use the LOOKUP() function to look backward that-many index values from the current mark.  In the LOOKUP calc I gave above, I used an offset of zero.  That says to look in the current mark.  But if I want to look 12 months prior, I would use -12.  Or if one month prior, I would use -1.  Etc.


          (This assumes you have your sheet set up to display by month.)


          A lot of the LOOKUP strategy depends on how your sheet is designed, so a specific answer for what you need to do will depend on what your sheet and data look like.