topic Re: Chart with x-axis autonumbering 1 to x in QlikView

Chart with x-axis autonumbering 1 to x

drollinson — Wed, 25 Nov 2020 16:16:04 GMT

Hi.

Imagine I have multiple libraries I'm monitoring over a month. I want to present a chart showing, say, time spent in the libraries by individual visitor. The chart would have [TimeSpent] on the y-axis, and the x-axis would be visitorsequencenumber (first visitor on left and last off to the right).

In my query/load script I have created two sequence fields. The first gives a sequence id for visits to an individual library; the second gives a sequence id for the entire dataset (libsequenceid and sequenceid).

NB for reference, visitors arriving at the same time in two or more libraries on a certain day are then ordered by their ID number. So overall the order in which data is sorted is first by date/time arrived and then by visitorid.

Using either of these sequence ids as the dimension works well if all I'm doing is presenting the chart for one library or for the entire dataset for a set time.

However, if I enable selections via list boxes because I want to be able to look at two libraries together, or just a particular date range, then the x-axis numbering no longer runs from 1 to x, but with the id numbers which match that range (e.g. 7, 11, 12, 19, 31, 58).

So, what I would like is rather than use one of my loaded sequence IDs, I would like the results to autonumber with the first visitor based on current selections being presented as 1, the second as 2, etc.

I'm thinking Synthetic Dimensions may help here, but I'm struggling with getting it to work. Can anyone help, please?

I'll upload a test file for information.

Thanks!

Re: Chart with x-axis autonumbering 1 to x

sunny_talwar — Tue, 26 Apr 2016 16:45:55 GMT

Try this as your calculated dimension:

=Aggr(RowNo(), LIBSEQUENCEID)

Re: Chart with x-axis autonumbering 1 to x

MarcoWedel — Tue, 26 Apr 2016 20:11:08 GMT

‌you could also use the rank() function.

regards

Marco

Re: Chart with x-axis autonumbering 1 to x

swuehl — Tue, 26 Apr 2016 20:24:41 GMT

Note that the aggr() function will sort the dimension values by load order by default.

Hence,

=Aggr(RowNo(), SEQUENCEID)

will not create necessarily create rowno = 1 for the smallest SEQUENCEID.

With latest QlikView versions, you can use structured parameter to get this sorted

=Aggr(RowNo(), (SEQUENCEID, (NUMERIC, ASCENDING)))

If you are using QV11.20, you may need to take care that your SEQUENCEID are correctly sorted in load order.

Re: Chart with x-axis autonumbering 1 to x

swuehl — Tue, 26 Apr 2016 20:37:53 GMT

And if you are using 11.20 and don't want to reorder your SEQUENCEIDs, you can use

=Aggr(Rank(-(DATEVISIT+TIMEVISIT),4,2), SEQUENCEID)

i.e. rank by lowest timestamp to identify the first visitors.

Re: Chart with x-axis autonumbering 1 to x

drollinson — Wed, 27 Apr 2016 13:43:32 GMT

Thank you all for your help.

My version of QV is still 11.20 so the suggestion of ensuring my data is sorted correctly when loading and then

using a calculated dimension of =Aggr(RowNo(), SEQUENCEID) was the one which worked for me.

Thanks again.

Much appreciated!

Re: Chart with x-axis autonumbering 1 to x

drollinson — Wed, 27 Apr 2016 13:47:45 GMT

Thank you for your help too. I marked the other item as the solution, but it was close between the pair of you. Thanks again.