topic Re: Pivot table- How do I break the column into 2 or more sub columns? in QlikView

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

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

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

sunny_talwar — Fri, 23 Sep 2016 14:38:31 GMT

Something like this?

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

neelamsaroha157 — Fri, 23 Sep 2016 14:45:47 GMT

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

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

nagarjuna_kotha — Fri, 23 Sep 2016 14:50:02 GMT

Check attached.

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

I hope this may help . 🙂

- Nagarjun

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

Anonymous — Fri, 23 Sep 2016 14:52:07 GMT

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

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

sunny_talwar — Fri, 23 Sep 2016 14:54:41 GMT

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

'Patient Service revenue'

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

sunny_talwar — Fri, 23 Sep 2016 14:56:32 GMT

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

Anonymous — Fri, 23 Sep 2016 15:04:03 GMT

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?

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

Anonymous — Fri, 23 Sep 2016 15:06:29 GMT

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.

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

nagarjuna_kotha — Fri, 23 Sep 2016 15:42:59 GMT

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

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

Anonymous — Fri, 23 Sep 2016 16:24:20 GMT

Thanks for the info.

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

Anonymous — Wed, 05 Oct 2016 15:13:29 GMT

Hi, is this possible to sort this list by Patients Service revenue amount (decreasing )only for the year 2016. ?

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

sunny_talwar — Wed, 05 Oct 2016 15:23:09 GMT

Used the following sort expression and sort descending:

Sum({<%year = {$(=Max(%year))}>}%Revenue)

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

Anonymous — Wed, 05 Oct 2016 16:01:51 GMT

Awesome! That Works!

Thank you

Pavithra Shetty