# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for
Did you mean:
Creator III

## Totals for most recent years in a pivot table

I want to make a pivot table which shows totals for 4 categories, within a range of years. At the top of the table, I want a total for the years before 2005 (or another year of choice).

fields are year, category, amount.

Example:

 year category 1 2 3 4 <2005 / other € 170.000 € 62.000 € 33.000 € 25.000 2005 € 10.000 € 7.500 € 6.000 €              - 2006 € 12.000 € 1.000 € 5.800 €              - 2007 € 13.000 € 800 € 5.700 € 100 2008 € 27.000 € 3.000 € 5.600 € 500 2009 € 27.500 € 3.500 € 5.500 € 400 2010 € 28.000 € 4.000 € 5.700 € 600 2011 € 29.000 € 5.000 € 5.800 € 800 2012 € 30.000 € 3.400 € 3.400 € 750 2013 € 28.000 € 3.500 € 2.800 € 900 2014 € 34.000 € 3.600 € 2.900 € 1.000 2015 € 50.000 € 4.000 € 3.100 € 1.050 2016 € 60.000 € 4.100 € 3.700 € 1.100

I saw that dimension limits in a pivot table is not possible. How should I go about achieving this?

Any help would be very welcome!

1 Solution

Accepted Solutions
MVP

May be this as calculated dimension:

If(Year < Year(Today()) - 10, 'Other', Year)

10 Replies
MVP

May be use a calculated dimension:

If(Year < 2005, '<2005/other', Year)

Anonymous
Not applicable

did you try with calculated Dimension?

if (year >= 2005, year, '<2005 / other')

Creator III
Author

Hmm, both answers could be right, I will try.. I was thinking along the path of set analysis, using rank() and / or aggr() in the sum() of amount...

MVP

Well using set analysis you would be able to restrict data, but i don't see how you would combine data into buckets using set analysis.

Creator III
Author

You have a point. So, I should indeed fiddle around with a calculated dimension. Suppose I would like to show only the most recent ten years and the rest as "other".. How shoud I do that?

MVP

Calculated dimension or you can may be create a new field in the script:

If(Year < 2005, Dual('<2005/other', 2004), Year) as NewYearDimension

FROM ....

and now you can use NewYearDimension as your chart dimension

MVP

May be this as calculated dimension:

If(Year < Year(Today()) - 10, 'Other', Year)

Creator III
Author

No, I need to still have all those years in other charts...

MVP

You will still have the Year dimension to use wherever you want to use. You are just creating a new field for this particular chart. No information is getting lost