2 Replies Latest reply on Jul 25, 2018 6:22 AM by Martin Lira

    Filter with Dimensions

    Martin Lira

      I've been trying to apply a filter using the year in this workbook to get the % difference compared with the last year. I managed to do a table calculation that allows me to filter it without affecting the table calculation that gives me the % difference but I can't figure out how to "link" the dimension "year"  to this actual calculation.

       

      I already have tried with the calculation" LOOKUP(ATTR([Year]),0)"  which helped me but as I mentioned before I can not manage to make it work when I filter by year. When I filter by year the table calculations disappear.

       

      I need to keep the filter by year because of other worksheets that use it. I would like to only keep one year filter which affects all the worksheets.

       

      Is there any way to do this calculation?

       

      Thanks!!

        • 1. Re: Filter with Dimensions
          swaroop.gantela

          Martin,

           

          I'm not sure if this will work for your true setup,

          but one method you could try is joining your dataset to itself.

          For the join fields, you could set copy 1 to be a calculated field of

          YEAR([Start Date])-1 and the other to be just YEAR([Start Date (Copy2)])

          This way each line of your dataset will have the previous year's value.

          Please see join screenshot below.

           

          This process creates duplications, so the actual current year run totals are:

          { FIXED [Start Date (Years)]:SUM(

          { FIXED [Start Date]:MIN( [Runs] ) } ) }

           

          and for the previous year:

          { FIXED [Start Date PrevYear (Years)]:SUM(

          { FIXED [Start Date (PrevYear)]:MIN([Runs (PrevYear) ] ) } ) }

           

          So the percent change will always be present despite any filtering of the year

          (copy 1 version):

          ([RunsPerYear]-[RunsPerPreviousYear]) / [RunsPerPreviousYear]

           

          Please see workbook v10.3 attached in the Forum Thread.

           

          276639year.png

          1 of 1 people found this helpful
          • 2. Re: Filter with Dimensions
            Martin Lira

            This is what I was looking for!! thank you!!