2 Replies Latest reply on May 10, 2018 12:51 PM by Andrew Chow

    Filtering columns differently in the same table

    Andrew Chow

      I want to be able to dynamically select the current year and comparison year for a table and show Current Year revenue and Variance against Compared Year.

       

      I've set up parameters for the Current Year and Comparison Year and tied the Current Year to the Year filter. But I'm afraid that I wouldn't be able to get the Comparison year information because we would be filtering against the year.

       

      So if I wanted to compare 2018 with 2016, I'd set my Current Year with 2018 and my Comparison Year as 2016. As of now, by Revenue for 2018 is correct, as the parameter is tied to the filter. But my Variance against Compared Year(2016) is blank, because I'm filtering out all of the 2016 data. Is there a way I can set up this comparison so that this is even possible? The different in years is dynamic, sometimes we'll compare 2018 v 2017, others 2018 v 2015.

       

      Thanks!

        • 1. Re: Filtering columns differently in the same table
          Brian Cronin

          Create a calculated field that looks like this and call it Current Year Revenue If Year = [Current Year Parameter] then [Revenue] else 0 then set up another calc field as Comparison Year Revenue as Year = [Comparison Year Parameter] then [Revenue] else 0 then Third calc that compares those 2 fields for your Variance calc.  That should get you the answer you're looking for.

          1 of 1 people found this helpful
          • 2. Re: Filtering columns differently in the same table
            Andrew Chow

            Thanks Brian!!

             

            A small follow up question: Is it then possible to have these parameters multi-select a filter for the rest of the dashboard? I have graphs outside of this table, and in this instance we would want the graphs to be filtered on the Current Year and the Comparison Year. So in the above example I would want to filter the year on 2018 and 2016. I know how to do one single comparison through the formula condition in the filter, but I'm not sure how to do multiple parameters.

             

            Thanks!!