Pivot table- How do I break the column into 2 or more sub columns?

[Anonymous]

I have a Pivot table with Provider name, their revenue and year. I want revenue to be split into years.

For example My table looks like this:

I want above table (Patient Service Revenue column) look like this:

I have attached example chart also.

[sunny_talwar]

Something like this?

[neelamsaroha157]

I am sure there would be more optimized method to this but here is what I know would work

Create a Inline in your script as 'Dim' with the values as Year & YTD

then

On Dimension tab

1. Provider

2. =Pick(Match(Dim, 'Year', 'YTD'),[YearField],'YTD')

Expression

Pick(Match(Dim,'Year','YTD'),

Your Expression1 for Patient Service Revenue ,

Your Expression2 for YTD Patient Service Revenue

)

Pivot your Table on calculated dimesion.

hope this helps..

[nagarjuna_kotha · Accepted solution]

Hi

Check attached.

Create caluculate dim or inline fielsd in backend.drag that to harizental

I hope this may help . 🙂

- Nagarjun

[Anonymous]

Yes, Correct. How did you do that? I forgot the settings.

Also, is it possible to make the label "Patient Service revenue" above year? (Just like excel do)

Thanks for your help

Pavithra Shetty

[sunny_talwar]

Look at nagarjuna.kothamandi‌'s sample where he used ValueList. But you can also create a new dimension like this

'Patient Service revenue'

[sunny_talwar]

[Anonymous]

Thank you So much. What does "Value List" mean? Just the value that we want to display? or is there any other use of it?

[Anonymous]

Thank you very much. I was struggling to drag the column into horizontal. Some how I was not pulling that to all the way to the end. I figured out!

Thank you for the quick reply. It's a great help to learn the tool.

[nagarjuna_kotha]

ValueList Use:

     1. We can create temp dimensions.

     2.Without reloading file we can create fileds in front End.

     3.Last bur not least , Its like inline load , we can create as much records as we can.

     4.Example : ValueList('Ram','Krish','Sri')

-Nagarjun

-