Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
drollinson
Contributor II
Contributor II

Chart with x-axis autonumbering 1 to x

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!

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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.

See also

The sortable Aggr function is finally here!

Attached the sample with added SEQUENCEID to the bar chart to demonstrate the difference.

2016-04-26 22_25_03-QlikView x64 - [C__Users_Stefan_Downloads_LibraryTest (1).qvw].png

View solution in original post

6 Replies
sunny_talwar

Try this as your calculated dimension:

=Aggr(RowNo(), LIBSEQUENCEID)


Capture.PNG

MarcoWedel

‌you could also use the rank() function.

regards

Marco

swuehl
MVP
MVP

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.

See also

The sortable Aggr function is finally here!

Attached the sample with added SEQUENCEID to the bar chart to demonstrate the difference.

2016-04-26 22_25_03-QlikView x64 - [C__Users_Stefan_Downloads_LibraryTest (1).qvw].png

swuehl
MVP
MVP

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.

drollinson
Contributor II
Contributor II
Author

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!

drollinson
Contributor II
Contributor II
Author

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.