Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
nehasaxena
Creator II
Creator II

Totals in Pivot table

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

10 Replies
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
Partner - Master II
Partner - Master II

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

I can walk on water when it freezes
Gysbert_Wassenaar

What's the problem?


talk is cheap, supply exceeds demand
nehasaxena
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

4 cyclic groups -

Dimensions.png

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