topic Pivot table totals and column references in QlikView

Pivot table totals and column references

Tue, 31 Aug 2010 04:55:47 GMT

Hi All,

I have a simple requirement which i hope you can help me with. The application was developed by a client and i'm trying to find the simplest solution.

See the pivot table below:

In column 6, 'Est Cost to Complete', I want to show only the positive values from the formula Column(8) - Column(5).

The existing formula is:

if(Column(8) - Column(5) < 0,0,Column(8) - Column(5))

You can see the pivot totals do not show the total of the values in the column, rather the expression is also performed at the total level.

Does anyone have a solution?

FYI

Column 8 formula is : sum(EHHBudget)

Column 5 formula is :

Sum({$<FinYear=, Month=>} if(ProjectTranDate >= '01/01/2010',ProjectTranAmount)) + Sum({$<FinYear=, Month=>} if(left(ProjectCode,4) = '70EH',POLineCommitmentAmount))

Pivot table totals and column references

Miguel_Angel_Baeyens — Tue, 31 Aug 2010 05:05:44 GMT

Hello Matt,

As a quick suggestion I'd use for colum 6

If([Restart Budget] - [Total Costs to Date] < 0, 0, [Restart Budget] - [Total Costs to Date])

to refer the row rather than using the whole column.

Hope that helps

Pivot table totals and column references

Tue, 31 Aug 2010 05:11:17 GMT

Miguel,

I tried the formula and found the same result as the screenshot attached in the original post.

Pivot table totals and column references

Miguel_Angel_Baeyens — Tue, 31 Aug 2010 06:08:29 GMT

Matt,

I got you wrong, you will need to play with dimensionality() function in your expression to show the grand total with different sums. Something like

If(dimensionality() = 0, sum(something), If(Column(8) - Column(5) < 0, 0, Column(8) - Column(5))

Dimensionality() allows you to write depending on the level of expressions.

Hope that helps

Pivot table totals and column references

Wed, 01 Sep 2010 02:37:25 GMT

It looks as if i can achieve the same with the rowno() function in this instance as dimensionality(), however it looks like a powerful function.

I'm not sure how to use it to sum the ordinary lines and ignore the total line calculation.

Pivot table totals and column references

Miguel_Angel_Baeyens — Wed, 01 Sep 2010 08:21:24 GMT

Hi Matt,

As I suggested in the sample code above, you will have to use a conditional in each expression where the rows differ from the totals, as I'm guessing you want to keep totals, but to calculate them in a different way.

Regards