2 Replies Latest reply on Nov 27, 2018 1:19 PM by Mackenzee Sims

    Dashboard Action - Two Most Recent Quarters

    Mackenzee Sims

      Hi All,

       

      Hoping one of you can help because I'm stumped - and not ensure what I want is entirely possible.

       

      I've got a bar chart that shows the average overall score by quarter. Then I've got a bottom chart that shows scores for sub questions by quarter. What I'd like to do is click on a quarter in the bar chart and have the bottom chart then show that quarter and the previous quarter. Like so:

       

      Also, when nothing is clicked, I'd like the bottom chart to default to the most two recent quarters (so in this case 2018 Q3 and Q4).

       

      I was able to make it show two quarters based on a quarter using a parameter and datediff. I also thought maybe some use of index. But nothing worked right.

       

      Does anyone have any ideas?

       

      Basic workbook with fake data attached as well.

        • 1. Re: Dashboard Action - Two Most Recent Quarters
          swaroop.gantela

          Mackenzee,

           

          I'm not sure if this will work for your true setup, but maybe it can give ideas.

           

          I first unioned the dataset to itself, which generated a new field called [Table Name].

           

          Then I created a field that will be used to link the quarter in the graph to the quarter in the table:

          DATE(IF [Table Name]="Sheet 1.csv" THEN DATETRUNC('quarter',[Date])

          ELSE DATEADD('quarter',1,DATETRUNC('quarter',[Date]))

          END)

           

          This takes advantage of the two copies of the table to generate one quarter and the quarter before.

           

          So the dashboard action is from Quarter(Date) on the Graph to QuarterLinkDate on the table

          which will pull up two months, one from one copy of the table, and then the previous month

          from the other copy of the table.

           

          The most recent quarters becomes:

          DATETRUNC('quarter',[QuarterLink])={MAX(DATETRUNC('quarter',[Date]))}

           

          Because it uses QuarterLink, it will pull up two most recent months.

           

          The last part is to show the two most recent months if nothing is selected.

          In that case, the Max Quarterlink is not equal to the Min Quarterlink, so:

          IF WINDOW_MAX(MAX([QuarterLink]))<>WINDOW_MIN(MIN([QuarterLink]))

          AND MAX([Most Recent])

          THEN "Show"    

          ELSEIF WINDOW_MAX(MAX([QuarterLink]))=WINDOW_MIN(MIN([QuarterLink]))

          THEN "Show2"

          END

           

          // With a Compute Using of "Table Down"

           

          Please see workbook v10.4 attached in the Forum Thread:

          Dashboard Action - Two Most Recent Quarters

           

          288637quartAct.png

          1 of 1 people found this helpful
          • 2. Re: Dashboard Action - Two Most Recent Quarters
            Mackenzee Sims

            This is great. Thank you! +