Pivot table- calculated columns

[aarohipatel]

Hi,

I have an application wherein I am using Pivot table with the directly available fields. But, I need some additional calculated columns. Attached is the sample data and following are the new columns I want in the table(highlighted in yellow):

In the excel file attached for sample data, I have added a sheet 'Results expecting' where in the calculation of the new columns is available.

Only for the latest Year Qtr , I need month wise.

Could you please advise on how to achieve the new columns?

Thank you

[aarohipatel]

@sunny_talwar  Could you please provide your inputs and/or  possible solution for my requirement?

Thank you!

[sunny_talwar · Accepted solution]

Check attached

[aarohipatel]

Hi Sunny,

Thank you very much for the solution you provided. It is exactly what I need.

When I tried to implement the same in one of the test application, I am not getting the Pivot table working. Could you please take a look at it (attached) an let me know where am I going wrong?

You have used the following expression in sort- What is 1E10?

(YEAR * 10000),
-Rank(YEARQTR),
Dim/100,
YEARMONTH/1E10
)

 One additional question, I have added a  pivot chart 'Sum' (CH02) in which I am grouping 2 expressions. When I move the YEARQTR field position to horizontal, the grouping symbol is missing. But when I keep YEARQTR vertical, grouping is working fine(CH04). Could you please advise on this as well?

Thank you much!

[sunny_talwar]

Pivot table? Why a pivot table?

IE10  = 10,000,000,000 (1 followed by 10 zeros)

For grouping, you need to add MONTH as the third dimension

[aarohipatel]

Hi Sunny,

Thank you for responding. I did not get the answer for the following:

In the attachment that I have,

1. Chart number 03: I tried to implement the same as you but it is not working. Can you please let me know where am I going wrong?

2. Chart Number 04 is working fine but when I change the YearQtr from Horizontal to vertical (which is Chart 02), the expression grouping symbol is missing and I am trying to get solution for that.

(Difference between chart 04 and 05 is position of the YEARQTR field causing the cyclic group symbol to miss.

Please advise.

Thank you much!