topic Last 12 values dimension limits in QlikView

Last 12 values dimension limits

tseebach — Fri, 17 Feb 2012 07:35:05 GMT

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

Fri, 17 Feb 2012 07:46:04 GMT

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

I dont know about qv11.

But you can do that using rank function.

try by rank()

may be it will help you.

Last 12 values dimension limits

CELAMBARASAN — Fri, 17 Feb 2012 07:47:41 GMT

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

jagan — Fri, 17 Feb 2012 07:58:09 GMT

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

tseebach — Fri, 17 Feb 2012 07:58:37 GMT

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

Last 12 values dimension limits

CELAMBARASAN — Fri, 17 Feb 2012 08:01:29 GMT

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

tseebach — Fri, 17 Feb 2012 08:20:48 GMT

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

tseebach — Mon, 20 Feb 2012 13:09:05 GMT

Bump, anyone an idea?

Last 12 values dimension limits

SunilChauhan — Mon, 20 Feb 2012 13:31:33 GMT

sort dimension in reverse order using sort and

the in presentation

max visible no 12

hope this help

Last 12 values dimension limits

tseebach — Thu, 23 Feb 2012 20:42:26 GMT

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

Last 12 values dimension limits

Tue, 06 Mar 2012 11:06:38 GMT

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

tseebach — Tue, 06 Mar 2012 11:21:04 GMT

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

Last 12 values dimension limits

Tue, 06 Mar 2012 11:24:41 GMT

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

Tue, 06 Mar 2012 13:03:38 GMT

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

Wed, 14 Mar 2012 12:51:39 GMT

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

Thu, 15 Mar 2012 10:21:15 GMT

A workaround that sometimes works is to:

1. Add a new expression with your dimension as the expression

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

beanmachine — Tue, 24 Jul 2012 09:25:42 GMT

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

Wed, 06 Mar 2013 15:04:14 GMT

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.

Re: Last 12 values dimension limits

Wed, 04 Dec 2013 14:01:40 GMT

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!