"Show only last n values" as dimension limit

[#1]

Hello,

I'm working with Qlikview 11 and I want to show only the last n values of a chart dimension (the values are strings, btw.), but unfortunately the "Dimension Limits" tabs only has "First n values" available. Reversing the sort order of the dimension is not an option since it is required that the values must be presented in ascending order from left to right.

Is there an alternative way to restrict the dimension values to only the n last ones?

Thanks in advance.

[Gysbert_Wassenaar]

You could add an expression FieldIndex('MyDim',MyDim) and promote it to first position so you can use dimension limits to restrict to the largest n. If that's not the order you need then you must have some logic to determine what the order is. You could use that to create a calculated dimension. Or add a field in the script with rowno() or recno() and use that.

[#3]

Hi Gysbert,

Thanks for your reply. I'm afraid I don't quite understand how I should use the FieldIndex function here or create the calculated dimension. Can you please elaborate or give an example?

Suppose my dimension contains values von 'A' to 'Z' and I only want to show the last n=5 columns 'V' - 'Z'. How should the expression or calculated dimension look like?

Adding a field in the script would be my last resort, since I really would like to make the chart dynamic by having an input box for selecting the value of n in my sheet.

Thanks a lot for your help.

[Gysbert_Wassenaar · Accepted solution]

See attached qvw for explanation of the FieldIndex expression.

The other option is to create a field in the script. This does not prevent your chart from being dynamic. You merely explicitly create an ordered numeric field for your dimension so you have something that can be used for dimension limits.

You mention n=5 last columns. That sounds like you're using a pivot table. Pivot tables do not have dimension limits as an option. You will have to create a calculated dimension. See the bottom chart in the attached qvw.

[#5]

Thanks for providing that great example. This has helped me understand a lot of things.