YTD calculation Issue

venkanna_info — Fri, 09 Jun 2017 09:13:47 GMT

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

Re: YTD calculation Issue

Sergey_Shuklin — Fri, 09 Jun 2017 17:19:59 GMT

Hello!

This will help:

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

PFA

venkanna_info — Sun, 11 Jun 2017 07:40:48 GMT

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 — Mon, 12 Jun 2017 16:32:36 GMT

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.