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

       

      Thanks,

       

      Benedikt