2 Replies Latest reply on Feb 14, 2017 2:04 AM by Tore Levinsen

    Show last x years

    Tore Levinsen

      Hi all.


      I have attached a workbook where my goal is to do the following:


      I would like the end user to be able to filter the data in the view (including sub totals) on either "All Years" or "the last X years" (3 years is set in the attached workbook). I would also like the users to be able to pick whatever years they like (i.e. 2012 and 2013)


      I've created a Parameter (Choose Period) which is either 3 (for 3 years) or 100 (which will give me all years).


      The logic is then in the "Last 3 years" measure (LAST () > [Choose Period]) - which I put on Filters and set to be true.


      In my sheet: "Last 3 years no totals" -> this works like a charm (I would like that the "Years" filter would be automatically updated as well - but let's not ask to much!).


      But when I add totals + sub totals, in sheet "Last 3 years with totals" -> I try the same "Choose period" parameter, select "last 3 years". Then I keep my data for the last 3 years (my dataset stop at 2015, so the last 3 years would be 15, 16 and 17). But for the earlier years (2012-2014) - I can still see the totals and the years -> but no data on the Ship Mode/Sub-Category level. I would like to filter everything away, as in the view without totals.


      What am I missing?



        • 1. Re: Show last x years
          James Austin

          Hi Tore,


          Try adding a calculated field along the lines of:


          DATEDIFF('year',[Order Date],Today())<[Choose period]


          and add this to the filter shelf setting to true.  Last part on the year quick filter set the option to Only relevant values.  This should get you close.


          At the moment you are using LAST() which is a table calculation and performed after the dimensional filters; essentially you are getting all years back in the results and then 'hiding' some of those years which is why the totals don't always work.


          Good Luck



          1 of 1 people found this helpful
          • 2. Re: Show last x years
            Tore Levinsen

            Hi James,


            an excellent solution - which seems to be working fine also with my original (and a bit more complex) view.