
Re: QV: limit Pivot table to three columns + show total values for second dimension
Karthik Srinivasan Apr 4, 2017 9:16 AM (in response to Benedikt Neumayer)Hi,
You can do this via set analysis. The current method of using IFMATCH 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!

Re: QV: limit Pivot table to three columns + show total values for second dimension
Benedikt Neumayer Apr 5, 2017 6:51 AM (in response to Karthik Srinivasan )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

Re: QV: limit Pivot table to three columns + show total values for second dimension
Karthik Srinivasan Apr 5, 2017 9:17 AM (in response to Benedikt Neumayer)Hi,
How about selecting 'Show Partial Sums' for Dimension2 also?

Re: QV: limit Pivot table to three columns + show total values for second dimension
Benedikt Neumayer Apr 5, 2017 3:49 PM (in response to Karthik Srinivasan )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.

Re: QV: limit Pivot table to three columns + show total values for second dimension
Karthik Srinivasan Apr 6, 2017 1:12 AM (in response to Benedikt Neumayer)Hi,
I'm not able to understand what the required output is, can you post a sample mockup/screenshot?




Re: QV: limit Pivot table to three columns + show total values for second dimension
Benedikt Neumayer Apr 6, 2017 4:05 AM (in response to Karthik Srinivasan )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

Re: QV: limit Pivot table to three columns + show total values for second dimension
Benedikt Neumayer Apr 12, 2017 4:11 PM (in response to Benedikt Neumayer)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

Re: QV: limit Pivot table to three columns + show total values for second dimension
Vishwarath Nagaraju Apr 12, 2017 5:38 PM (in response to Benedikt Neumayer)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. ?

Re: QV: limit Pivot table to three columns + show total values for second dimension
Anil Babu Apr 12, 2017 9:22 PM (in response to Benedikt Neumayer)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))

Re: QV: limit Pivot table to three columns + show total values for second dimension
Benedikt Neumayer Apr 13, 2017 2:22 AM (in response to Anil Babu )Dear Anil,
thanks for the suggestion, but it does not give the expected result. Let me reexplain 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

Re: QV: limit Pivot table to three columns + show total values for second dimension
Benedikt Neumayer May 11, 2017 9:59 AM (in response to Benedikt Neumayer)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




