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

[Anonymous]

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

Thanks,

Benedikt

[Anil_Babu_Samineni]

I assumption, Max can return correct value only. Here the problem is when it selection happen one Year filter i think Max won't consider as we expected due this algorithm made up into only more than three years

And, Your question is you want to default as 3 Years data at a time. And after that when filter happen you need to show only filter or filter + last 2 years - Here, Can you describe more. May try below

=If(GetFieldSelections(Year)>1 , Sum({<Year = {'$(=GetFieldSelections(Year))'}>}sales), sum({<Year={">=$(=max(Year,3))"}>} sales))

[Anonymous]

Dear Anil,

thanks for the suggestion, but it does not give the expected result. Let me re-explain the problem:

My Data:

Event	Year	Sales
Event A	2017	100
Event A	2016	200
Event A	2015	300
Event B	2017	400
Event B	2016	500
Event C	2017	600

The table should by default select the three most recent years of data (working fine with your solution or the one suggested by Karthik earlier for Event A). Now only problem is that if I Events B or C are selected, the table does not show any results, whereas it should then simply show the most recent 2 or 1 year(s).

Anil, your solution works fine if I select only one year, but not if by the selection of the Event there is only data for 2 or 1 years.

Hope that makes it clear, thanks anyways for your input.

Benedikt

[Anonymous]

Hi all,

trying to get the topic back to the top once again, as I have not been able to find a solution yet.

Anybody?

Thanks a lot

Benedikt