Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Experts,
I'm in dire need of your assistance with the following issue.
I want to retrieve data from a table to fetch values from previous years and determine the growth YoY.
As of now, the table looks like this:
Year | Region | Make | Model | Sales |
2020 | North America | Ford | Fiesta | 1000 |
2020 | Japan | Nissan | Altima | 1500 |
2020 | Europe | Hyundai | Elantra | 800 |
2021 | North America | Ford | Fiesta | 1100 |
2021 | China | Nissan | Altima | 1600 |
2021 | Europe | Hyundai | Elantra | 900 |
2022 | North America | Ford | Fiesta | 1200 |
2022 | China | Nissan | Altima | 1700 |
2022 | Europe | Hyundai | Elantra | 1000 |
2023 | North America | Ford | Fiesta | 1300 |
2023 | China | Nissan | Altima | 1800 |
2023 | Europe | Hyundai | Elantra | 1100 |
2024 | North America | Ford | Fiesta | 1400 |
2024 | China | Nissan | Altima | 1900 |
2024 | Europe | Hyundai | Elantra | 1200 |
I need the result to be something along the lines of the following table:
Year | Region | Make | Model | Sales | Last Year Sales |
2020 | North America | Ford | Fiesta | 1000 | |
2020 | Japan | Nissan | Altima | 1500 | |
2020 | Europe | Hyundai | Elantra | 800 | |
2021 | North America | Ford | Fiesta | 1100 | 1000 |
2021 | China | Nissan | Altima | 1600 | 1500 |
2021 | Europe | Hyundai | Elantra | 900 | 800 |
2022 | North America | Ford | Fiesta | 1200 | 1100 |
2022 | China | Nissan | Altima | 1700 | 1600 |
2022 | Europe | Hyundai | Elantra | 1000 | 900 |
2023 | North America | Ford | Fiesta | 1300 | 1200 |
2023 | China | Nissan | Altima | 1800 | 1700 |
2023 | Europe | Hyundai | Elantra | 1100 | 1000 |
2024 | North America | Ford | Fiesta | 1400 | 1300 |
2024 | China | Nissan | Altima | 1900 | 1800 |
2024 | Europe | Hyundai | Elantra | 1200 | 1100 |
I've tried using Above(), and offset the number of rows to get the correct value for each year, however, once I apply filters, the values are incorrect.
The end result would be a line chart, showing YoY growth, that can be filtered by region, make or model.
Thank you!
Hi Joakimnn,
What you need to do is the following:
Table:
Load
Year &'|'& Region &'|'& Make &'|'& Model as %Table
Year,
Region,
Make,
Model,
Sales
From [YourSource] (qvd);
Left Join (Table)
Load
(Year+1) &'|'& Region &'|'& Make &'|'& Model as %Table
Sales
From [YourSource] (qvd);
Now you have joined your sales to the table.
Second option is to go for a mapping load and return 0 if there is no sales for the previous year. In the first solution you will get a null().
Jordy
Climber