Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have this table and would like to create a line (or dot) chart with the years on the x axis and the sum of the relevant "spend" column as the points on the y axis. Since I don't have explicit year information anywhere else I can't really find out how to do it.
There will be more of those colums in the next years if that's relevant.
Hi,
One approach to this would be to use the CrossTable() function when you load this data into Qlik:
This would transform your table from a wide format to a long format, a bit like the following.
Category | Amount |
Spend (EUR) 2019 | 20344828 |
Spend ($) 2019 | 29500000 |
Spend (M$) 2019 | 29.5 |
Spend (EUR) 2020 | 6896552 |
Spend($) 2020 | 10000000 |
…. | …. |
You could then extract the Year into its own field using something like right(Category, 4) as Year.
At this point, your chart should be simpler to create
Thanks
Ben