topic Re: Totals in Pivot table in QlikView

Totals in Pivot table

nehasaxena — Tue, 29 Mar 2016 07:50:04 GMT

Dear Experts,

I am facing problems with displaying total in Pivot table. I have already gone through various posts in community but not found the solution for my issue. Here is my expression -

=If(Month='JAN',Sum({$<TypeofData={'Actual'},Year={'Pr-Live'}>}$(vExpr_UnitValue))/1000,if(Unit='Mio Litres',Sum({$<Year={'Pr-Live'}>}RF022016Ltr)/1000,Sum({$<Year={'Pr-Live'}>}RF022016)/1000))

Table shows the total of Feb to Dec data and not including the value of Jan.

I understood that I need to use aggr function but how it fits in my expression can somebody guide me?

Thanks.

Best regards,

Neha

Re: Totals in Pivot table

avinashelite — Tue, 29 Mar 2016 07:59:27 GMT

Your using this expression in the DIMENSION tab or expression ??

QV is case sensitive check the JAN and Jan are different could you please check the same

Re: Totals in Pivot table

ali_hijazi — Tue, 29 Mar 2016 08:01:29 GMT

did you try to write your expression as follows:

Sum({$<TypeofData={'Actual'},Month={'jan'},Year={'Pr-Live'}>}$(vExpr_UnitValue))/1000)

Sum({$<Year={'Pr-Live'},Unit={'Mio Liters'}>}RF022016Ltr)/1000)

Sum({$<Year={'Pr-Live'}>}RF022016)/1000)

the '+' here is treated as Union

Re: Totals in Pivot table

Gysbert_Wassenaar — Tue, 29 Mar 2016 08:09:56 GMT

What's the problem?

Re: Totals in Pivot table

nehasaxena — Tue, 29 Mar 2016 08:35:13 GMT

Hi Avinash,

I am writing the expression in Expression tab. JAN is correct because I am getting data for Jan Month. But the problem is in sub total. I have enabled the check box Show Partial Sums for Month Dimension and it is giving totals for all the months except JAN.

Regards,

Neha

Re: Totals in Pivot table

sunny_talwar — Tue, 29 Mar 2016 08:38:17 GMT

Try something like this:

Sum(Aggr(

If(Month='JAN',Sum({$<TypeofData={'Actual'},Year={'Pr-Live'}>}$(vExpr_UnitValue))/1000,if(Unit='Mio Litres',Sum({$<Year={'Pr-Live'}>}RF022016Ltr)/1000,Sum({$<Year={'Pr-Live'}>}RF022016)/1000))

,YourChartDimensions))

Re: Totals in Pivot table

nehasaxena — Tue, 29 Mar 2016 09:12:31 GMT

I am having 4 cyclic groups and Month in the dimension tab. I tried the below expression

Sum(Aggr(

If(Month='JAN',Sum({$<TypeofData={'Actual'},Year={'Pr-Live'}>}$(vExpr_UnitValue))/1000,if(Unit='Mio Litres',Sum({$<Year={'Pr-Live'}>}RF022016Ltr)/1000,Sum({$<Year={'Pr-Live'}>}RF022016)/1000))

,Month))

Its not working.

Re: Totals in Pivot table

sunny_talwar — Tue, 29 Mar 2016 09:14:44 GMT

You have 4 cycle groups or you have 1 cycle group with 4 fields?

Can you share the name/s of your cycle group?

Re: Totals in Pivot table

nehasaxena — Tue, 29 Mar 2016 09:21:12 GMT

4 cyclic groups -

Re: Totals in Pivot table

sunny_talwar — Tue, 29 Mar 2016 09:24:02 GMT

And all of the the 5 dimensions are active at all times (no conditional hide/show)? Try this:

Sum(Aggr(

If(Month='JAN',Sum({$<TypeofData={'Actual'},Year={'Pr-Live'}>}$(vExpr_UnitValue))/1000,if(Unit='Mio Litres',Sum({$<Year={'Pr-Live'}>}RF022016Ltr)/1000,Sum({$<Year={'Pr-Live'}>}RF022016)/1000)), $(=GetCurrentField(grGlobal)), $(=GetCurrentField(grGlobal1)), $(=GetCurrentField(grGlobal2)), $(=GetCurrentField(grGlobal3)), Month))

Actually add these as well:

Sum(Aggr(

If(Month='JAN',Sum({$<TypeofData={'Actual'},Year={'Pr-Live'}>}$(vExpr_UnitValue))/1000,if(Unit='Mio Litres',Sum({$<Year={'Pr-Live'}>}RF022016Ltr)/1000,Sum({$<Year={'Pr-Live'}>}RF022016)/1000)), $(='[' & GetCurrentField(grGlobal) & ']'), $(='[' & GetCurrentField(grGlobal1) & ']'), $(='[' & GetCurrentField(grGlobal2) & ']'), $(='[' & GetCurrentField(grGlobal3) & ']'), Month))

Re: Totals in Pivot table

nehasaxena — Tue, 29 Mar 2016 10:14:38 GMT

Unfortunately this is not working