topic Add yearly value per dimension field in script in QlikView

Add yearly value per dimension field in script

pvanderkolk — Thu, 19 Mar 2020 13:01:06 GMT

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

Re: Add yearly value per dimension field in script

sunny_talwar — Thu, 19 Mar 2020 13:49:12 GMT

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 ];

Re: Add yearly value per dimension field in script

pvanderkolk — Fri, 20 Mar 2020 09:45:55 GMT

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.