Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Add yearly value per dimension field in script

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

1 Solution

Accepted Solutions
Highlighted

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

View solution in original post

2 Replies
Highlighted

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

View solution in original post

Highlighted
Partner
Partner

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.