How to get a Rolling 12 Months Line Chart from the beginning data point?

[BoXiangWang]

Hi there, I'm new to the QlikSense.

I wouId like to know if there is a way to make a line chart with each data point as the R12M turnover rate, without creating a separate table in the data model?

The definition of R12M turnover rate = sum(Last 12 month turnover) / [avg(Last 12 month Actual Headcount)

If I use RangeSum, only the last data point will be the cumulation, but I want all data points to be R12M.

Fig1. Line chart made by Excel.

Fig2. Table made in QS

Only the last row has the Rolling 12 Months total.

Here is the expression that I'm using:

(
Rangesum(Above(

sum(aggr(count(distinct {<action_group={'Termination'},
[date_month]={">=$(=AddMonths(vRollingDate,-vRollingMonths+1)) <=$(=vRollingDate)"}>} [dim_ppl_employee_sk]), date_month))
, 0,12))
/
(
Rangesum(Above(

sum(aggr(count(distinct {<payroll_status={'A'}, MonthEndInd={1},
[date_month]={">=$(=AddMonths(vRollingDate,-vRollingMonths+1)) <=$(=vRollingDate)"}>} [dim_ppl_employee_sk]), date_month))

, 0,12))
/12
)
)

[Kushal_Chawda]

@BoXiangWang  what is the expression for turnover and actual headcount?

[BoXiangWang]

Hi Kushal,

Sum of Turnover of the Last 12 Months:

Rangesum(Above(sum(aggr(count(distinct {<action_group={'Termination'},
[date_month]={">=$(=AddMonths(vRollingDate,-vRollingMonths+1)) <=$(=vRollingDate)"}>} [dim_ppl_employee_sk]), date_month))
, 0,12))

Average Actual Headcount of the Last 12 Months:

( Rangesum(Above( sum(aggr(count(distinct {<payroll_status={'A'}, MonthEndInd={1},
[date_month]={">=$(=AddMonths(vRollingDate,-vRollingMonths+1)) <=$(=vRollingDate)"}>} [dim_ppl_employee_sk]), date_month)) , 0,12)) /12 )

[Kushal_Chawda]

@BoXiangWang  tyr below

=sum(aggr(Rangesum(Above(sum(aggr(count(distinct {<action_group={'Termination'},
[date_month]={">=$(=AddMonths(vRollingDate,-vRollingMonths+1)) <=$(=vRollingDate)"}>} [dim_ppl_employee_sk]), date_month))
, 0,12)), (date_month,(NUMERIC))))

Average Actual Headcount of the Last 12 Months:

sum(aggr( Rangesum(Above( sum(aggr(count(distinct {<payroll_status={'A'}, MonthEndInd={1},
[date_month]={">=$(=AddMonths(vRollingDate,-vRollingMonths+1)) <=$(=vRollingDate)"}>} [dim_ppl_employee_sk]), date_month)) , 0,12)) /12 , (date_month,(NUMERIC))))