Forum Discussion
Stacked column chart
Good day,
I have a dataset as per below structure covering 10+ years and 1000's of rows,
Name | Date | Yr 1 | Yr 2 |
Opp A | 02-01-2020 | 9 | 25 |
Opp B | 10-01-2019 | 3 | 11 |
and am looking to plot a stacked column chart using 'Yr 1' values for the year of 'Date' and 'Yr 2' for the following year.
| 9 |
|
3 | 11 | 25 |
2019 | 2020 | 2021 |
Even better would be to plot by month ie. for 'Opp A' plot 9 / (remaining months in year = 11) for 2020 while 25 / 12 months for 2021. This would allow the users to drill down on the date field.
You might think about un-pivoting your incoming data
Name
Date
Calculated Year
Opp A
02-01-2020
Yr 1
9
02-01-2020
Opp A
02-012020
Yr 2
25-
02-01-2021
Then a measure could just say, if Yr 1 use the date as is, if Yr 2 add one year to the date
2 Replies
You might think about un-pivoting your incoming data
Name
Date
Calculated Year
Opp A
02-01-2020
Yr 1
9
02-01-2020
Opp A
02-012020
Yr 2
25-
02-01-2021
Then a measure could just say, if Yr 1 use the date as is, if Yr 2 add one year to the date
Thank you for your reply.
Un-pivoting works a treat and combined with a calcualted column for the date the correct chart can be produced. 👍
Any ideas about how to go down to month level, ie. show the average by month instead of the yearly totals (9 and 25) ?
The final outcome should allow the user to drill down from year to quarter and month on the chart.