topic Re: Create Years between Two Dates in QlikView

Create Years between Two Dates

qlikwiz123 — Sat, 16 Nov 2024 18:52:46 GMT

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

Re: Create Years between Two Dates

albertovarela — Thu, 26 Mar 2020 22:04:06 GMT

Please take a look at the attached qvw

Re: Create Years between Two Dates

qlikwiz123 — Fri, 27 Mar 2020 12:45:54 GMT

Exceptional. Thank you so much 🙂

Re: Create Years between Two Dates

qlikwiz123 — Fri, 27 Mar 2020 16:39:34 GMT

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:

Re: Create Years between Two Dates

albertovarela — Fri, 27 Mar 2020 17:09:46 GMT

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.

Re: Create Years between Two Dates

qlikwiz123 — Fri, 27 Mar 2020 17:33:32 GMT

@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

Re: Create Years between Two Dates

albertovarela — Fri, 27 Mar 2020 17:42:32 GMT

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

Re: Create Years between Two Dates

qlikwiz123 — Fri, 27 Mar 2020 19:31:59 GMT

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

Re: Create Years between Two Dates

qlikwiz123 — Tue, 31 Mar 2020 17:21:00 GMT

@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.