while copy data from 1 year to other i have to get the avg for a kpi before copying

[guvvalasairam436]

There is  a requirement to copy data from one particular year to other other years one month since we have are using avg kpi values of our source date is getting aggregated , to get the average i can divide that value with 12(no.of months) i will the avg if i have values for all the dates , IN my case i don't have values for few months in that case i cant divide it with 12 , so i tried to use datediff function i have to use previous function in the date diff to get the expected year its shows as syntax error can some one help me.

As mentioned in the below attachments i need to copy 2025 data in 202612  , if i have the data for all the months them i can directly make it as divide  by 12 , but we are not sure for how many months we will be having the data , i need to read the no. of months has the data in 2025 

[N1kh1l · Accepted solution]

@guvvalasairam436 

Do not divide by fixed 12 but rather count the period for populated data and then divide.

In your excel demonstration you fail to show details of other dimensions for the KPI. So Its an aggregated view of other dimensions. If KPI is  stored on # for other dimensions except time and version then you do not need t aggregate them in step 1

MEMBERSET [d/Account]="KPI"

VARIABLEMEMBER #Period of [d/Account]

VARIABLEMEMBER #KPI of [d/Account]

VARIABLEMEMBER #KPIAVG of [d/Account]

//Step1

DATA([d/Account]=#KPI, [d/Dim1]="#", [d/Dim2]="#")=RESULTLOOKUP() // Aggregate on all dimensions other then time and version on # members.

//Step2

DATA([d/Account]=#Period)=RESULTLOOKUP([d/Account]=#KPI)*0+1

DATA([d/Account]=#KPIAVG)=RESULTLOOKUP()/RESULTLOOKUP([d/Account]=#Period) // You can store it on an Actual account, if you want to copy it using copy package else you can copy in this Data Action itself.

Hope this helps !!

Nikhil