Qlik Community

Ask a Question

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 
Search instead for 
Did you mean: 
hansdevr
Creator III
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:

   

yearcategory1234
<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
sunny_talwar

May be this as calculated dimension:

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

View solution in original post

10 Replies
sunny_talwar

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')

hansdevr
Creator III
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...

sunny_talwar

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.

hansdevr
Creator III
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?

sunny_talwar

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

LOAD Year,

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

FROM ....

and now you can use NewYearDimension as your chart dimension

sunny_talwar

May be this as calculated dimension:

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

View solution in original post

hansdevr
Creator III
Creator III
Author

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

sunny_talwar

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