Current and Previous Month Values in IDT?

[Former Member]

Hi Experts,

i have a table in IDT and it contains Value and Period like below.

My requirement is i need to add one more calculated column in the table and name it as Previous Period value and i need to populate values for this field according to previous period. Like in Jan value is 20 and Feb value is 30 these value has to populate in Previous period value like below.

Please help me on this.

Period	Value	Prev period value
01.2015	20	0
02.2015	30	20
03.2015	40	30

Thanks

[former_member207052 · Accepted solution]

1. Create an alias for the table and make a join with the expression: Alias_of_Sudha.Datemy=dateadd(mm,-1,Sudha.Datemy)
   Outer join on the original table
2. In your business layer drag and drop the "value" field from the alis table and rename it as say, Previous month Value"
3. If you try creating a query you will see the required results.

In case if your Period column in not in Date format, use format functions to make it as a date.

[Former Member · Accepted solution]

Hi Narasimhan,

Thank you for the reply. I am really not sure how to convert Period (01.2015) into date format.

I am getting  [MySQL][ODBC 5.3(w) Driver][mysqld-5.6.24]FUNCTION .dateadd does not exist.

please help me on this.

Thanks

[former_member207052 · Accepted solution]

Try the following join expression:

DATE_ADD(STR_TO_DATE(yourCoulum,"%m.%Y"),INTERVAL -1 MONTH)

[Former Member · Accepted solution]

Hi Narasimhan,

The logic you provided is not working. its giving same values in Alias tables as original values not previous month values.

Let me know if i have to use any other logic to achieve this.

Thanks

[former_member207052 · Accepted solution]

Can you post the select statement which selects month, value and previous month value? you can either use IDT Query or create a simple web-I report to get the SQL.

Notes:

1. Confirm that the previous month value measure is based on the alias table column and not from your main table.
2. Make sure that the data conversion used here STR_TO_DATE(yourCoulum,"%m.%Y") is working fine by directly running the query against your db table (using toad like tool) and verify that you get expected results.

[Former Member · Accepted solution]

Hi Narasimhan,

Please find below details.

1. Join Condition i used

Alias_of_MOL_OBJECTIVE_KPI_VIEW.period=PERIOD_ADD(DATE_FORMAT(STR_TO_DATE(MOL_OBJECTIVE_KPI_VIEW.period, '%m/%Y'),'%Y%m'),-1)

2. In Main Table side i selected Outer Join and i put cardinality as n:n

3. I am getting below result while running in IDT. "Previous Month Kpi Value" is from Alias Table and remaining "Period" and "Kpi Value" is from Main table.

4. Below is the SQL Script for above result.

SELECT

  MOL_OBJECTIVE_KPI_VIEW.period,

  MOL_OBJECTIVE_KPI_VIEW.kpi_value,

  Alias_of_MOL_OBJECTIVE_KPI_VIEW.kpi_value

FROM

  (

  SELECT

  MOL_OBJECTIVE_KPI.obj_id,

  MOL_OBJECTIVE_KPI.kpi_id,

  MOL_OBJECTIVE_KPI.period,

  MOL_OBJECTIVE_KPI.kpi_value,

  MOL_OBJECTIVE_KPI.target_value,

  MOL_OBJECTIVE_KPI.business_analysis,

  MOL_OBJECTIVE_KPI.measure_type,

  MOL_OBJECTIVE_KPI.created_date,

  MOL_OBJECTIVE_KPI.kpi_name,

  MOL_OBJECTIVE.obj_name,

  MOL_OBJECTIVE.obj_description,

  MOL_OBJECTIVE.responsible_person,

  MOL_OBJECTIVE.department,

  MOL_OBJECTIVE.objective_type,

  MOL_OBJECTIVE.dashboard_name

FROM

  MOL_OBJECTIVE INNER JOIN MOL_OBJECTIVE_KPI ON (MOL_OBJECTIVE.obj_id=MOL_OBJECTIVE_KPI.obj_id)

  )  Alias_of_MOL_OBJECTIVE_KPI_VIEW RIGHT OUTER JOIN (

  SELECT

  MOL_OBJECTIVE_KPI.obj_id,

  MOL_OBJECTIVE_KPI.kpi_id,

  MOL_OBJECTIVE_KPI.period,

  MOL_OBJECTIVE_KPI.kpi_value,

  MOL_OBJECTIVE_KPI.target_value,

  MOL_OBJECTIVE_KPI.business_analysis,

  MOL_OBJECTIVE_KPI.measure_type,

  MOL_OBJECTIVE_KPI.created_date,

  MOL_OBJECTIVE_KPI.kpi_name,

  MOL_OBJECTIVE.obj_name,

  MOL_OBJECTIVE.obj_description,

  MOL_OBJECTIVE.responsible_person,

  MOL_OBJECTIVE.department,

  MOL_OBJECTIVE.objective_type,

  MOL_OBJECTIVE.dashboard_name

FROM

  MOL_OBJECTIVE INNER JOIN MOL_OBJECTIVE_KPI ON (MOL_OBJECTIVE.obj_id=MOL_OBJECTIVE_KPI.obj_id)

  )  MOL_OBJECTIVE_KPI_VIEW ON (Alias_of_MOL_OBJECTIVE_KPI_VIEW.period=PERIOD_ADD(DATE_FORMAT(STR_TO_DATE(MOL_OBJECTIVE_KPI_VIEW.period, '%m/%Y'),'%Y%m'),-1))

Please let me know if you need any additional details.

Thanks

[former_member207052 · Accepted solution]

I think the issue is because, in your Join, you are formatting your Right hand side value as date but you keep your Left hand side format as varchar.

Try with the below formula, where both lhs and rhs are in date formats.

DATE_FORMAT(STR_TO_DATE(Alias_of_MOL_OBJECTIVE_KPI_VIEW.period,'%m/%Y'),'%Y%m')=PERIOD_ADD(DATE_FORMAT(STR_TO_DATE(MOL_OBJECTIVE_KPI_VIEW.period, '%m/%Y'),'%Y%m'),-1)

[former_member207052 · Accepted solution]

Also, make sure you have a outer join on the base table as shown in my screenshot(refer my initial post)

[Former Member · Accepted solution]

Hi Narasimhan,

I got values in previous as below but its duplicating rows. Please have a look into it.

Thanks

[former_member207052 · Accepted solution]

The results are correct as your source data has duplicate values. For example for February, you have two values i.e., 26 and 22 and hence you two values for the same month from the alias table as well.

If you want to avoid, you have to use aggregation (like max, Min, sum, average, etc) or bring additional columns in the grouping. Check with the business on which values (or on what basis) they would like to calculate the previous month value.

[Former Member · Accepted solution]

Hi Narasimhan,

i got the correct result by using below code

DATE_FORMAT(STR_TO_DATE(Alias_of_MOL_OBJECTIVE_KPI_VIEW.period,'%m/%Y'),'%Y%m')=PERIOD_ADD(DATE_FORMAT(STR_TO_DATE(MOL_OBJECTIVE_KPI_VIEW.period, '%m/%Y'),'%Y%m'),-1)

AND Alias_of_MOL_OBJECTIVE_KPI_VIEW.kpi_id=MOL_OBJECTIVE_KPI_VIEW.kpi_id

Thanks a lot for the help.