Add yearly value per dimension field in script

[pvanderkolk]

Dear Qlik fans,

I want to add the number of days off to all employees for each year, but I'm not succeeding in this task.

In the inline script you find the number of days off per employee. After each fifth year after the start of a contract the employee is rewarded with an additional day off. When allis realized in the script, I can calculate the cumulative number of days off from 2018 front end (Sum({$<Year={'>=2018'}>}TotalAnnualDaysOff).

As you can see in the qvw I'm able to add the number of days off when there is a year without a new contract. However, the arrival of new employee shouldn't mean that the employees who already had a contract don't get days off anymore; there shouldn't be any replacements, the annual days off for every employee individually just needs to continue.

I am confident that we don't need to copy the already existing script for each employee and then concatenate all into one table. I don't think the existing code is the quickest way to achieve the goal either, so feel free to adjust it.

Many thanks in advance!

Pim

[sunny_talwar · Accepted solution]

Do you may be need this?

Sample:
LOAD *,
	 Year(Contract) as YearOfContract,
	 Year(Contract) + IterNo() - 1 as ContractStart,
	 AnnualDaysOff + Floor((IterNo()-1)/5) as TotalAnnualDaysOff
While Year(Contract) + IterNo() - 1 <= Year(Today(1));
LOAD * INLINE [
    RelGid, Contract, AnnualDaysOff
    903, 01-05-1996, 14
    1623, 01-10-1995, 10
    2460, 01-07-2014, 14
    2554, 01-10-2014, 14
    2760, 19-02-2017, 14
];

[pvanderkolk]

Thanks a lot Sunny, that is exactly what I needed! I was afraid that Iterno() would increase the reload time heavily in the actual dashboard, but that's not the issue.