Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Tags (1)
4 Replies

Re: Barchart show last n items sorted by dimension instead of measure

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

Re: Barchart show last n items sorted by dimension instead of measure

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

Re: Barchart show last n items sorted by dimension instead of measure

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

Re: Barchart show last n items sorted by dimension instead of measure

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

Community Browser