3 Replies Latest reply on Apr 3, 2019 5:28 PM by Jacques Cordelier

    Dynamic Calculations with Parameter Dates

    Jacques Cordelier

      Hey there,

       

      I have a data set where, in SQL, we coded a fixed Start Grade for a Customer at a specific point in time

      The data then has a new grade for the Customer per month going forward

      Each Grade has a value against it(1,2,3,4...) per grade and this we used to calculate the Grade Movement, for all the months based on the Initial Start Grade

      What we want to do now is have a Dynamic Start Point witch I created using a parameter based on the available dates

       

      My problem , how do I allocate a 'new' start Grade to correspond to the new Dynamic Date and do all calculations based on the New Grade

      I even went as far as to try and blend a second Data set ...

       

      Many Thanks

      Jacques

        • 1. Re: Dynamic Calculations with Parameter Dates
          Cédric Tran

          Hello Jacques,

           

          I open the discussion because I am not sure I have the correct picture of the problem.

           

          As you have a SQL DB, what I can tell you is:

          - parameter could be pass through the DB with custom SQL Datasource

          - calculated field could be calculated based on parameter value

           

          Does this help ?

           

          Cedric

          • 2. Re: Dynamic Calculations with Parameter Dates
            Jacques Cordelier

            Hey Cedric,

             

            Scenario A (current)

            If the in Jan 2016 the Customers Grade is B all grade movements in the months after wards will be calculated on that specific Month and Grade

            So if Jun 2016 is a D, the movement is -2

            Then if Jan 2017 is an A, the movement is +1, as it is based on Jan 2016's B

            Then if Jun 2017 is a B, the Movement is 0 ..... and so on

             

            Scenario B (want)

            If I then move my Start date to Jun 2016, the Grade will now be D

            So,  Jan 2017 is an A, the movement now is +3

            And Jun 2017 is a B, the Movement is +2 ....and so on

             

            My predicament is that when I move my Start date, using the parameter, as per Scenario B, my calculations are still looking at the Grade in Scenario A, which is a B and not the required D

             

            PS - I am running a Custom SQL query as my Data Source, Extract, not Live

             

            Thanks for your assistance

             

            Jacques

            • 3. Re: Dynamic Calculations with Parameter Dates
              Jacques Cordelier

              Hey there,

               

              I have found a work around that works for my current situation

              I had to LEFT JOIN the same Custom SQL query to itself on the Customer field (the Customer filed has to be unique though)

              This will then create Two sets of Dimension on your worksheet

              You then use Either Data set as the Dynamic Set of Values, and the other Data set will follow the Calculations accordingly, as per what you selected in the First Data set

               

              Thanks

              Jacques