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

[karthiksrqv]

Hi,

You can do this via set analysis. The current method of using IF-MATCH is not maintainable (when an additional year's data comes up in your dataset).

Keep your dimension as just Year. If your expression is sum(sales) , instead, use this:

=sum({<Year={">=$(=max(Year,3))"}>} sales)

For the 2nd requirement,

in the Properties > Presentation , select only the first dimension, and check the 'Show Partial Sums' option.

Hoping these help!

[Anonymous]

Hi Karthik,

thanks for your swift reply, Regarding the first problem, your solution seems to work really fine.

Regarding the 2nd requirement, I had already tried this, however, the only result I get from activating the partial sums for dimension 1 is this:

However I would need a total for Dimension 1 split up for each of the entries in dimension 2.

Kind regards,

Benedikt

[karthiksrqv]

Hi,

How about selecting 'Show Partial Sums' for Dimension2 also?

[Anonymous]

Hi,

then the result looks like the table I have posted in the original question. I really think that the name "partial  sum" does not yield partial sums for the respective dimension but simply a total.

[karthiksrqv]

Hi,

I'm not able to understand what the required output is, can you post a sample mockup/screenshot?

[Anonymous]

Hi,

The desired output can be seen in the Excel File I attached to my initial post. Moreover please see a screenshot here below:

Meaning I am looking to have a total, summing up all options for Dimension 1 but still splitting up for dimension 2.

KR and thanks very much for your persistence

Benedikt

[Anonymous]

Hi Karthik,

I am also coming back to your solution on the three columns: The formula you suggested has the problem that if there is data only for one Year, the Pivot table does not show anything.

Do you happen to know a way to circumvent this error?

Thanks again,

Benedikt

[Anonymous]

Dear Karthik, dear All,

Sorry to insist on this, but I am really struggling to find a solution to the remaining problem here:

Using this expression for my table

=sum({<Year={">=$(=max(Year,3))"}>} sales)

the table correctly filters to the three most recent years of data, however if with a certain data filter there is only data for two years or one year, the table does not show any data.

Does anybody have a possible variation of the expression to circumvent this problem?

Thanks a thousand times

Benedikt

[vishsaggi]

I vaguely remember sunny has done something like this before. You can ask him, if he can pinch in some time to work on this. Hey  stalwar1‌ any help here. ?