topic Headings that span multiple columns in a pivot table in QlikView

Headings that span multiple columns in a pivot table

Anonymous — Thu, 20 Apr 2017 18:01:50 GMT

Hi all,

I've got a pivot table that has three month actuals and budget and also total and variance columns. These columns are all defined individually in the expressions as this allows me to add the variances.

I currently have text boxes to span the actuals, budget and variance columns, but obviously any changes in the column widths requires manual changes to the boxes.

I am therefore looking to include these headings in the table itself.

Any ideas?

Thanks!

Each column is defined separately:

Re: Headings that span multiple columns in a pivot table

krishnacbe — Thu, 20 Apr 2017 19:38:33 GMT

Hi,

Do you have two Separate values as Actuals and Budget or Same Base amount you are defining as Actual and Budget based on some field.

Share me any one expression you have used.

Re: Headings that span multiple columns in a pivot table

Anonymous — Thu, 20 Apr 2017 19:41:39 GMT

The only way I've done it is with a concatenated type fact able with the variance calculated in the script. Something like below.

You would add both Month and Type as a dimension. You would only have 1 expression then =Sum(Amount)

Once the pivot table is created, you would move the Type dimension accross the top.

Month	Type	Amount
1-Jan	Actual	500
1-Feb	Actual	400
1-Mar	Actual	300
1-Jan	Budget	1000
1-Feb	Budget	2000
1-Mar	Budget	3000
1-Jan	Variance	-500
1-Feb	Variance	-1600
1-Mar	Variance	-2700

Re: Headings that span multiple columns in a pivot table

vinieme12 — Thu, 20 Apr 2017 19:57:04 GMT

Here is a sample app showing how to,

But you will need change table type to a pivot table and nest all your expressions into a single expression

Re: Headings that span multiple columns in a pivot table

Anonymous — Thu, 20 Apr 2017 20:04:17 GMT

Here is an example.

Re: Headings that span multiple columns in a pivot table

prachisangewar — Fri, 21 Apr 2017 07:01:23 GMT

Hi,

Pls find attached the solution. Create 3 seperate measures in the Pivot table.

Thanks

Re: Headings that span multiple columns in a pivot table

Anonymous — Fri, 21 Apr 2017 08:03:24 GMT

Thanks for your help Vineeth,

I have tried using the dimension: =ValueList('Jan A','Feb A','Mar A','Total YTD actuals','Jan B','Feb B','Mar B','Total YTD budget') and the expression as below. If I can get this to work I will then look into the variances using the same method.

However, it doesn't seem to work, can you see what I might be doing wrong?

Thanks!

Re: Headings that span multiple columns in a pivot table

Anonymous — Fri, 21 Apr 2017 08:10:18 GMT

My data is rows of transactions that are either actual or budget.

This is how my variance is calculated:

=sum({$<[Period]={$(CurrentMonth)},[Year] = {2017}, Actual={Actual} >}([Base amount]/FX))-sum({$<[Period]={$(LastMonth)},[Year] = {2017}, Actual={Actual} >}([Base amount]/FX))

thanks

Re: Headings that span multiple columns in a pivot table

Anonymous — Fri, 21 Apr 2017 08:11:52 GMT

Thanks but this only works if I want variances for all months, whereas I'm just looking for MTD and YTD variance columns.

Re: Headings that span multiple columns in a pivot table

vinieme12 — Fri, 21 Apr 2017 13:09:14 GMT

can you post the app ? it's easier that way

you can scramble the data, refer to the below post

Preparing examples for Upload - Reduction and Data Scrambling

Re: Headings that span multiple columns in a pivot table

vinieme12 — Fri, 21 Apr 2017 13:10:43 GMT

Oh, Just spotted You have an "=" sign which is not needed on this line

= if(Actual = 'Budget',

remove = sign from here

Re: Headings that span multiple columns in a pivot table

Anonymous — Fri, 21 Apr 2017 14:38:45 GMT

The only way I can think of at the moment is to calculate it all in your script. So you end up with a table in your data model like this.

Month	Type	Amount
1-Jan	Actual	500
1-Feb	Actual	400
1-Mar	Actual	300
1-Jan	Budget	1000
1-Feb	Budget	2000
1-Mar	Budget	3000
Total	Budget	6000
Total	Actual	1200
YTDVariance	Variance	-4800
MTDVariance	Variance	-2700

And build your pivot table like in the attached example.

Re: Headings that span multiple columns in a pivot table

vinieme12 — Fri, 21 Apr 2017 15:51:36 GMT

connocmay

if your query is resolved , please close the thread

Qlik Community Tip: Marking Replies as Correct or Helpful