topic Re: How to display expression value only at subtotal level in pivot table. in QlikView

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

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

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.

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

rubenmarin — Fri, 29 Apr 2016 11:15:54 GMT

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

If(Dimensionality()=0, YourExpression)

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

trdandamudi — Fri, 29 Apr 2016 11:16:08 GMT

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

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

jagan — Fri, 29 Apr 2016 11:16:59 GMT

Hi,

Try like this

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

Regards,

Jagan.

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

abc_18 — Fri, 29 Apr 2016 11:29:56 GMT

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.

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

rubenmarin — Fri, 29 Apr 2016 11:35:52 GMT

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?

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

abc_18 — Fri, 29 Apr 2016 11:47:48 GMT

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.

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

rubenmarin — Fri, 29 Apr 2016 12:10:34 GMT

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.

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

abc_18 — Mon, 02 May 2016 05:37:25 GMT

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

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

sunny_talwar — Mon, 02 May 2016 07:08:23 GMT

We don't see the subtotal in your second image

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

abc_18 — Mon, 02 May 2016 07:13:50 GMT

Yes because I haven't selected any cost code to display subtotal value.

Only I have applied Dimensionality() formula.

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

rubenmarin — Mon, 02 May 2016 07:29:30 GMT

Hi Shikha, I will need a sample to help yo check why the table with many codes doesn't show subtotals, usual behaviour will show a subtotal for each code... Is the same table?