Aggregating multiple dimensions on line graph

[johnnyjohn]

Hi,

This is a bit of a tricky one to explain, do let me know if anything is unclear.

So I have data that looks like the following

What I am trying to accomplish here is to create a line graph that would look like this

However, given how the data exists, I'm not sure how to do this, because the line graph can only be given one dimension (which given the data above would have to be just one of the 5 time buckets), so I need to be able to somehow aggregate these different time buckets into one dimension so that it can work as intended.

I guess one solution here is to write my loader so that somehow my data ends up like this, in which case I can use my "time bucket" as dimension and "reversion measure" as measure, but I dont know how to do this, and am not even sure it is the best way to approach this problem.

Anyone have any ideas how to solve this?

Thanks!!

[anthonyj · Accepted solution]

Hi,

The Crosstable function is fantastic for this sort of thing. It will take the column headers and pivot them so they will perform exactly what you have supplied above.

Here's the documentation with examples from the Qlik site.

https://help.qlik.com/en-US/sense/May2021/Subsystems/Hub/Content/Sense_Hub/LoadData/work-with-cross-...

Here's the code as per your example:

data:
load * inline [
OrderID, -2s, -1s, 0s, 1s, 2s
aaa, -2.30, -2.20, -0.10, -1.10, 1.80
bbb, -0.52, -0.86, -0.06, 0.45, 0.40
ccc, -0.30, -0.23, -0.06, 0.23, 1.78

];

cross:
crosstable([Time Bucket], [Reversion Measure],1)
Load
OrderID,
[-2s],
[-1s],
[0s],
[1s],
[2s]
Resident data;

Which creates a table like this:

[johnnyjohn]

Yep, that did it, thank you sir