8 Replies Latest reply on Feb 5, 2019 9:19 AM by Jim Dehner

    Base year user select

    Christoph Klavehn

      Dear Community,

      I am using a simple table calculation "difference from" to show the changes from the base year (2015) for each country in the attached workbook. Now I would like to give the user the option to choose a different base year, e.g. 2017, so that the caclulation uses the selected year as its basis. Is this possible with a parameter / calculated field combo?
      Many thanks!

        • 1. Re: Base year user select
          Ken Flerlage

          One option would be to do the following:

           

          1) Change the table calc to compute as follows:

          2) Add a filter on year and set it up as an "At Least" filter.

          Then show that filter. The users will then be able to select their starting year and the table calculation should automatically adjust based on the first year in the view.

          See attached.

          1 of 1 people found this helpful
          • 2. Re: Base year user select
            Jim Dehner

            Good morning

            see the attached approach

            I looked at you book - you "year" is loaded a a number - not a date

            I converted it to a real date - dates can be used in trend plots, date calculations and have are treated a little different in table calculations -

            then I used the date as a continuous Year in the plot as shown below

             

             

            now the filter will control the start date (could also use a parameter and a filter

             

             

            Jim

             

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            1 of 1 people found this helpful
            • 3. Re: Base year user select
              Christoph Klavehn

              Ken, Jim,

              thanks much for your quick help!
              One more question: How can I have the user manipulate the "relative to" part in the table calculation? Using year as filter disables the years (as it should) but I would like to keep the whole timeline in view (2015, 16, 17, 18 - rather than toggling the start date of the view) and only change the reference year. See below, where the reference year is set on 2017.

               

              • 4. Re: Base year user select
                Jim Dehner

                Sorry I don't think that Ken or I got that from the post - but see the attached based on my original book where I converted you rfield to a real date

                 

                add a parameter

                add a calculation to determine the offset

                 

                and the formula

                 

                 

                set like this

                 

                Jim

                If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

                1 of 1 people found this helpful
                • 5. Re: Base year user select
                  Christoph Klavehn

                  Jim, many thanks and that's exactly as I wanted it to be. Sorry if that wasn't clear in my initial post.

                  Very best, Chris

                  • 6. Re: Base year user select
                    Jim Dehner

                    Glad to help out

                    Jim

                    • 7. Re: Base year user select
                      Christoph Klavehn

                      Jim, as a follow-up: is there a way to calculate the difference in [value] between 2018 and selected base year? I'd like to use this calculation as an extra info shown in the marks or tooltip

                      • 8. Re: Base year user select
                        Jim Dehner

                        this is the year difference for the offset

                         

                         

                        (you could also use  year( {fixed:max(date)})   in place of 2018

                         

                        then the difference is

                         

                        drop it on the tooltip an you get this

                         

                         

                        Jim

                        If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.