18 Replies Latest reply: Dec 4, 2013 9:01 AM by Jose Manuel Sánchez

# Last 12 values dimension limits

Hi, does anyone know how to get Dimension Limits to show the last 12 values in a chart rather than the first 12. I've tried -12, which gives af nonsense result.

In QV 10 it was possible using the Max Visible Number function under presentation. But In QV11 it does not seem to work.

• ###### Last 12 values dimension limits

Hi

yes we can do this by using max visible value in qv 10.

But you can do that using rank function.

try by rank()

• ###### Last 12 values dimension limits

Hi,

Did you checked with the dimension limits tab of the chart properties?Under show only drop down-->First,Largest,Smallest options

Celambarasan

• ###### Last 12 values dimension limits

That is the exact issue, I can only select First, Largest and Smallest. I need something like Last.

• ###### Last 12 values dimension limits

Hi,

Can you tel me the expression of yours?You need to done it with Expression itself like

Sum({<Customer={"=Rank(-Sum(Sales))<11"}>})

Hope it helps

Celambarasan

• ###### Last 12 values dimension limits

I know I could do that. But that would not work since its a rolling 12 months aggr, summing the first 12 values to the value of first value.

The expression is similar to this but a bit more complex:

rangesum(above(

Sum({\$<KPI={"Quality_Plans"},[Rec Type Name]={"Actual"},[Date (FK)]={">=\$(=Floor(MonthStart(Max([Date (FK)]),-24))) <=\$(=Floor(MonthStart(Max([Date (FK)]),0)))"},[Calender MonthName]= ,[Calender Year]=>} Quantity)

,0,12))

/

rangesum(above(

Sum({\$<KPI={"Quality_Plans"},[Rec Type Name]={"Target"},[Date (FK)]={">=\$(=Floor(MonthStart(Max([Date (FK)]),-24))) <=\$(=Floor(MonthStart(Max([Date (FK)]),0)))"},[Calender MonthName]= ,[Calender Year]=>} Quantity)

,0,12))

• ###### Last 12 values dimension limits

Bump, anyone an idea?

• ###### Last 12 values dimension limits

sort dimension in reverse order  using sort  and

the in presentation

max visible  no 12

hope this help

• ###### Last 12 values dimension limits

Close, but then the timeline goes from right to left, needs to left to right..

• ###### Re: Last 12 values dimension limits

Here is a solution we use to replicate what was available in QV10 and earlier in QV11.

• Switch off any dimension limits
• In the Presentation tab, tick 'Enable X-Axis Scrollbar'
• Set 'When Number of Items Exceeds' to a the level that suits you
• Tick 'Reversed'

This will show you the last X (for instance, months) in the correct order.  You can scroll back to the left to show lower values but the default will be to show the last X values from the maximum.

This seems to do the trick although it would be easier if the Dimension Limits included a 'Last' setting to replicate what we had before the upgrade.

• ###### Re: Last 12 values dimension limits

This worked perfectly for me.

I have a chart with a variable dimension (day, week, year) and I wanted to show only the last 10 days (or weeks or years) and this did the trick.

Thank you very much!

• ###### Last 12 values dimension limits

But if your dimension is simple for example year&month you can use "Largest" instead of the desired "Last". In these cases

Largest and = (Max(PeriodCounter) - Min(PeriodCounter) + 1) solves my case.

Olof Laurin, Senior Consultant at Tacticus AB, http://www.tacticus.se

• ###### Last 12 values dimension limits

Hi,

In QV 11 you can do this by using the settings in Chart Properties -> Dimension Limits tab.  Using First, Largest, Smallest options you can configure as you wish.  Also instead of fixed number you can also suggest the percentage, this is available in Qlikview 11.

Regards,

Jagan

• ###### Last 12 values dimension limits

Any news on this? I'm having exactly the same problem with my R12-figures showing only the last year. They worked fine until upgrading from QV10 to QV11.

Olof Laurin, Senior Consultant at Tacticus AB, http://www.tacticus.se

• ###### Last 12 values dimension limits

Sorry no, we running QV10 for now with clients that have any types of rolling charts.

• ###### Last 12 values dimension limits

I did some more testing and I think it is a bug that Largest works directly because it should take Largest of first expression values but at least for line charts it ignores that and uses Largest for first of dimensions instead.

But for tables it works as expected, the Largest acts on the first expression, but it is easy to add your dimension yearmonth also as an expression, then the dimension limit Largest works on that. Also for tables the feature on presentation show max number of values works excellent to clip the future tail of R12-values, for example =count(DISTINCT yearmonth). This does not seem possible on line charts.

The dimension limits would be easier to understand and use if it incorportaed both Last and the Show Max Number of values.

Olof Laurin, Senior Consultant at Tacticus AB, http://www.tacticus.se

• ###### Last 12 values dimension limits

I just discovered we have this problem on a couple of our reports. Have you found solution? Has Qlikview issued a response to this problem. Seems like a major oversight not have a Dimension limit of Last.

• ###### Last 12 values dimension limits

A workaround that sometimes works is to:

2. Put the new expression first.

3. Make it invisible (uncheck display option or on presentation tab depending on graph type)

4, Set dimension limit to Largest of first expression.

However if you have a set-expression involving your dimension the results seems somewhat unpredicatable (or at least I havn't understood what's going in).

Olof Laurin, Senior Consultant at Tacticus AB, http://www.tacticus.se

• ###### Re: Last 12 values dimension limits

Hello,

Elly had sent a test scenerio of the rolling 12 months and I noticed that you are generating calendar values in a spreadsheet that you then pull into QV.  The data associated to MonYearCalName is recognized as a text value therefore when you sort it will be alphabetically rather than chronologically.  This can be corrected by generating a master calendar in QV which will create the value as a date or numeric value.  Once that is done, choosing Largest 12 in the Dimension Limits tab should give you what you want.

There are several samples of setting up master calendars posted in the community.