Totals in Pivot table

[nehasaxena]

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

[avinashelite]

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

[ali_hijazi]

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

[Gysbert_Wassenaar]

What's the problem?

[nehasaxena]

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

[sunny_talwar]

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

[nehasaxena]

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.

[sunny_talwar]

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

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

[nehasaxena]

4 cyclic groups -

[sunny_talwar]

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