How to display expression value only at subtotal level in pivot table.

[abc_18]

Hi,

I have one expression field Variance%

Formula is :- Variance%= EAC-Budget / Budget.

in this image I have budget data only for Vendor C, so for that that row variance is -100%.

My requirement is how to get that variance value only at subtotal level, that is only -74% should display in report.

[rubenmarin · Accepted solution]

Hi Shikha, last total usually has Dimensionality=0, you can check this in the expression:

If(Dimensionality()=0, YourExpression)

[trdandamudi]

Did you give a try with Dimensionality() function ...

[jagan]

Hi,

Try like this

=IF(RowNo() = 0, 'Your Variance Expression')

Regards,

Jagan.

[abc_18]

Hi Ruben,

Thanks a lot for your reply.

I am getting the output , but only when I click to get subtotal. otherwise in variance column blank data is coming.

[rubenmarin]

Hi Shikha, sorry but I don't understand the behaviour, where you click to get the subtotals? Can you upload a sample or screenshot to check the issue?

[abc_18]

If I click on Cost Code 456 then in subtotal I am getting some value in variance% column ,but if cost code is not selected then Variance column is appearing blank.

I have attached sample screenshot.

[rubenmarin]

Still not sure, I will need a sample to give a tested answer...Maybe with:

If(Dimensionality()<Max(Dimensionality()), YourExpression)

If it helps, you can add a column with just Dimensionality() to check the values when you select code and set the condition properly.

[abc_18]

Hi Ruben,

After loading the data in qlikview with variance formula  (EAC-Budget / Budget ), report is like this.

But once I apply this formula to get variance only at subtotal level

If(Dimensionality()=0, (EAC-Budget / Budget )) on variance% column  then Variance column is appearing blank no data.

but if I click on any cost code (because partial sum is based on cost code ) to get subtotal then variance is appearing at subtotal level.

My question is that, is it possible to show variance even if we don't click on subtotal level ??

[sunny_talwar]

We don't see the subtotal in your second image