Create Years between Two Dates

[qlikwiz123]

Hi,

I have a table 

ID	EndYear	Cost
1	2025	50
2	2038	100

Result Table should be 50/6  for ID 1 and distributed across years until 2025 (6 years from today to 2025 including current year)

and for ID 2, it is 100/19 (2020 to 2038 including 2020 is 19 years) and distributed over years until 2038.

ID	EndYear	Cost
1	2020	8.3
1	2021	8.3
1	2022	8.3
1	2023	8.3
1	2024	8.3
1	2025	8.3
2	2020	5.26
2	2021	5.26
2	2022	5.26
2	2023	5.26
2	2024	5.26
2	2025	5.26
2	2026	5.26
.	.	.
.	.	.
.	.	.
2	2038	5.26

[albertovarela · Accepted solution]

Please take a look at the attached qvw

[qlikwiz123]

Exceptional. Thank you so much 🙂

[qlikwiz123]

Hi @albertovarela 

When I try to use the same logic on my real and bigger data set, it throws me an error

Syntax error

Unexpected token: 'Cost', expected one of: ',', ':', 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'OPERATOR_MULTIPLICATION', 'OPERATOR_DIVISION', 'OPERATOR_STRING_CONCAT', ...

Cost:
LOAD
1 as ID,
2020 + IterNo() -1 as EndYear,
as >>>>>>Cost<<<<<<
AutoGenerate
(1)
While
2020 + IterNo() - 1 <=

--------

The DEBUG Mode gives me this:

[albertovarela]

You don't need the square brackets enclosing DISPOSITION YEAR in the definition of vL_EndYear.  Also, try adding the Let Statement before the variables.

[qlikwiz123]

@albertovarela 

I removed the Square Brackets and added Let

I think the problem here is with the Term 'Cost' either as Table Name or Field Name. If you see the image below, ID and End Year has values but Cost comes up as ' as Cost' which is strange

[albertovarela]

ah! Look at the End Year on your screenshots; It's 2019. This  means that field Cost_Year can have negatives!

The quick fix - on line 188  update to:  '$(vL_Cost)' as Cost

Alberto

[qlikwiz123]

Thank you, will try this and hopefully don't have to get back to you again 🙂

[qlikwiz123]

@AlbertoV 

Sorry to bother you but is there a way we can adjust this logic at month level too?

Lets say if ID 1 has EndDate in 2025 in February, the Cost must be adjusted in years based on number of months in each year. Meaning, the total cost of 50 for ID 1 should be calculated only for 10 months in 2020, 12 months each until 2024 and 2 months in 2025.