12 Replies Latest reply: May 11, 2017 9:59 AM by Benedikt Neumayer RSS

    QV: limit Pivot table to three columns + show total values for second dimension

    Benedikt Neumayer

      Dear all,


      I am trying to produce the attached excel tables in QV and facing two problems while doing so:


      Firstly, I must limit the Column dimension "Year" to the three most recent years where there is content in the database. Right now the dimension "year" is programmed as follows:


      =if(match(Year,'2013','2014','2015','2016','2017'), Year)

      If for a specific selection there is no data for 2014 and 2016, the outcome is exactly as needed, however I am looking for a way to have only 2015, 2016 and 2017 showing if there is data for all five years. I am not sure whether this can be solved within the options of the pivot table (unlikely in my opinion) or whether this must / can be solved with a specific formula in the "Year" dimension.



      Secondly, the Excel table contains a total for the first dimension (column B) that is split up into all entries in the second dimension (column C). I am only finding a way to yield totals for the first dimension and/or the second dimension but not like in the excel table the results for each Country (second dimension) across all Programme types (first dimension).


      Duration New.PNG