YTD calculation Issue

[venkanna_info]

Hi All

Pls help below scenario

From the given data source (HeadcountDataSource.qvd), please transform the data into a structure described in table Output. Details per following:

Data Source Description

Column Name	Description
CompanyCode	Company code
CostCenter	Cost center code
KPI_ACTUALITY	Actuality of the values e.g. Actual (AC), Budget (BU), Forecast (FC) figures
KPI_PERIOD	Date representing Year and Month of data, data format is yyyy-mm-01
VALUESMonthly	Number of employees (Monthly) of corresponding CompanyCode, CostCenter, Period, and Actuality

Output

Column Name	Description
CompanyCode	Company code
CostCenter	Cost center code
KPI_ACTUALITY	Actuality of the values e.g. Actual (AC), Budget (BU), Forecast (FC) figures
KPI_PERIOD	Date representing Year and Month of data, data format is yyyy-mm-01
VALUESMonthly	Number of employees (Monthly) of corresponding CompanyCode, CostCenter, Period, and Actuality
VALUESYTD	An average number of employees per month from the beginning of year until the month (month value in KPI_PERIOD column)

Example

Data Source

CompanyCode	CostCenter	KPI_ACTUALITY	KPI_PERIOD	VALUESMonthly
8000	1130	AC	2009-01-01	13
8000	1130	AC	2009-02-01	11
8000	1130	AC	2009-03-01	11
8000	1130	AC	2009-04-01	11
8000	1130	AC	2009-05-01	11
8000	1130	AC	2009-06-01	11
8000	1130	AC	2009-07-01	11
8000	1130	AC	2009-08-01	11
8000	1130	AC	2009-09-01	10
8000	1130	AC	2009-10-01	9
8000	1130	AC	2009-11-01	9
8000	1130	AC	2009-12-01	9

CompanyCode	CostCenter	KPI_ACTUALITY	KPI_PERIOD	VALUESMonthly	VALUESYTD
8000	1130	AC	2009-01-01	13	13
8000	1130	AC	2009-02-01	11	12
8000	1130	AC	2009-03-01	11	11.666666666667
8000	1130	AC	2009-04-01	11	11.5
8000	1130	AC	2009-05-01	11	11.4
8000	1130	AC	2009-06-01	11	11.333333333333
8000	1130	AC	2009-07-01	11	11.285714285714
8000	1130	AC	2009-08-01	11	11.25
8000	1130	AC	2009-09-01	10	11.111111111111
8000	1130	AC	2009-10-01	9	10.9
8000	1130	AC	2009-11-01	9	10.727272727273
8000	1130	AC	2009-12-01	9	10.583333333333

[Sergey_Shuklin]

Hello!

This will help:

rangeavg(above(sum(VALUESMonthly),0,RowNo()))

PFA

[venkanna_info]

Hi Sergey,thanks for your kind revert.

But it doesn't giving proper answer with rangeavg function.

Kinly recheck as per data mentioned and do the needful.

thanks,

Venkanna

[Sergey_Shuklin]

Can you please provide more expanded answer? I'm don't getting what is wrong...

The result is VALUEYTD it brings an average of epmloyees from 1st month to the end of the year.