Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

connocmay
New Contributor

Headings that span multiple columns in a pivot table

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:

12 Replies
krishnacbe
Valued Contributor III

Re: Headings that span multiple columns in a pivot table

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.

atkinsow
Valued Contributor II

Re: Headings that span multiple columns in a pivot table

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.

MonthTypeAmount
1-JanActual500
1-FebActual400
1-MarActual300
1-JanBudget1000
1-FebBudget2000
1-MarBudget3000
1-JanVariance-500
1-FebVariance-1600
1-MarVariance-2700
vinieme12
Esteemed Contributor II

Re: Headings that span multiple columns in a pivot table

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

PivotGroupedHeader.JPG

atkinsow
Valued Contributor II

Re: Headings that span multiple columns in a pivot table

Here is an example.

prachisangewar
Contributor

Re: Headings that span multiple columns in a pivot table

Hi,

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

Thanks

connocmay
New Contributor

Re: Headings that span multiple columns in a pivot table

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!

connocmay
New Contributor

Re: Headings that span multiple columns in a pivot table

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

connocmay
New Contributor

Re: Headings that span multiple columns in a pivot table

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

vinieme12
Esteemed Contributor II

Re: Headings that span multiple columns in a pivot table

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

Community Browser