Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Barchart show last n items sorted by dimension instead of measure

Load * Inline

[

ROOM,ROOM_VALUE

Room1,7

Room2,12

Room3,9

Room4,2

Room5,8

Room6,1

Room7,6

];

How can I limit the bar chart to show last five rooms (Room3 to Room7)?

I tried limitation but it is based on measure value instead of dimension

4 Replies
Gysbert_Wassenaar

Create a RoomNumber field first:

Load *, Num#(Replace(ROOM,'Room')) as RoomNumber Inline

[

ROOM,ROOM_VALUE

Room1,7

Room2,12

Room3,9

Room4,2

Room5,8

Room6,1

Room7,6

];

Then change your chart expression to use that RoomNumber field. For example sum(ROOM_VALUE) becomes sum({<RoomNumber={'>$(=Max(RoomNumber,-5))'}>} ROOM_VALUE)


talk is cheap, supply exceeds demand
Not applicable
Author

Hi,

You can use the Autonumber() function

In your load script, do

Load *,Autonumber(ROOM) as Key Inline

[

ROOM,ROOM_VALUE

Room1,7

Room2,12

Room3,9

Room4,2

Room5,8

Room6,1

Room7,6

];

And then in the expression of your bar chart, write the expression as

Sum({$<Key={'>$(=Max(Key)-5)'}>}ROOM_VALUE)

Regards,

Rohan

Not applicable
Author

Thanks, but this only work if the dimension has specific format (i.e. ends with number), any solution to work on other dimension value?

Not applicable
Author

Hi,

Even if your dimension contains all alphabets, it works fine. As far as the dimension values are different, it will work.

Try this load script and use the same expression defined above i.e. Sum({$<Key={'>$(=Max(Key)-5)'}>}ROOM_VALUE)

Load *,Autonumber(ROOM) as Key Inline

[

ROOM,ROOM_VALUE

RoomA,7

RoomB,12

RoomC,9

RoomD,2

RoomE,8

RoomF,1

RoomG,6

];

You will get the same result i.e. last 5 Rooms result will be displayed.

Regards,

Rohan