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

# Dynamic Calculations with Parameter Dates

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

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

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

Jacques

• ###### 3. Re: Dynamic Calculations with Parameter Dates

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