Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Pivot table Partial total is incorrect

Hi,

I have read a lot of similar questions in community. But I feel my issue here is different.

The Total number is wrong in my "REMAINING BUDGET AS OF MAY 17 " column. and the expression is listed below:

=IF(RequestStatusDescription='CLOSED'or RequestStatusDescription='CLOSED AT PLANT' ,0,([CAPEX BUDGET CY]+[CAPEX BUDGET PY]-[TOTAL SPENT AS OF MAY 17]))

And the three columns([CAPEX BUDGET CY] , [CAPEX BUDGET PY], [TOTAL SPENT AS OF MAY 17]) shows correct total, no matter in straight table or pivot table.

So I am wondering the issue is with the if condition part.. (IF(RequestStatusDescription='CLOSED'or RequestStatusDescription='CLOSED AT PLANT')

FIY:

expression in [CAPEX BUDGET CY] is:

sum(if(EstimatedStartDate1>=dateStartYear,AFECapitalRequestAmount,0))

expression in [CAPEX BUDGET PY] is:

sum(if(EstimatedStartDate1<dateStartYear,AFECapitalRequestAmount,0))

expression in [TOTAL SPENT AS OF MAY 17] is :

sum( {$<PostingPeriod={5},ZFIYear={$(intCurrentYear)}>} Amount )+
sum( {$<PostingPeriod={4},ZFIYear={$(intCurrentYear)}>} Amount )+
sum( {$<PostingPeriod={3},ZFIYear={$(intCurrentYear)}>} Amount )+
sum( {$<PostingPeriod={1},ZFIYear={$(intCurrentYear)}>} Amount )+
sum( {$<PostingPeriod={2},ZFIYear={$(intCurrentYear)}>} Amount )+sum( {$<ZFIYear={2016}>} Amount )+sum( {$<ZFIYear={2015}>} Amount )+sum( {$<ZFIYear={2014}>} Amount )

I found out the difference between wrong pivot total and right total is related to those 'CLOSED' and 'CLOSED AT PLANT' items.

Capture.PNG

1 Reply
shanky1907
Creator II
Creator II

I am having a similar kind of issue. But i didn't understand what your analysis was.

My issue:

Show all values in Pivot/Straight Table (irrespective of current selection)