Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
venkanna_info
Partner - Contributor
Partner - Contributor

YTD calculation Issue

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

3 Replies
Sergey_Shuklin
Specialist
Specialist

Hello!

This will help:

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

PFA

venkanna_info
Partner - Contributor
Partner - Contributor
Author

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
Specialist
Specialist

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

avg_pic.png

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