topic Aggregating multiple dimensions on line graph in App Development

Aggregating multiple dimensions on line graph

johnnyjohn — Wed, 28 Jul 2021 02:07:20 GMT

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!!

Re: Aggregating multiple dimensions on line graph

anthonyj — Wed, 28 Jul 2021 04:36:09 GMT

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-tables.htm

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:

Re: Aggregating multiple dimensions on line graph

johnnyjohn — Wed, 28 Jul 2021 08:21:54 GMT

Yep, that did it, thank you sir