Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Please take a look at the attached qvw
Exceptional. Thank you so much 🙂
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:
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.
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
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
Thank you, will try this and hopefully don't have to get back to you again 🙂
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.