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: 
hildacgg
Creator
Creator

Totals in columns

I have a forecast table with budget for each quarter, and actual sales for each quarter, but when calculate my actual sales it is also accumulating the sales for part numbers that are not in my budget. In order to get the right total I have to check 'Sum of Rows' instead of 'expression total' but when I calculate the percentage = actual / budget * 100, my totals at the top is wrong...how can I resolve this? please help.

5 Replies
settu_periasamy
Master III
Master III

Hi,

Can you post the sample and expected output?

hildacgg
Creator
Creator
Author

My total budget is : 50,000 total mode 'Expression Total'

my total sales is : 35,250  with  'sum of rows' total mode

and is 42,300 with 'Expression Total' total mode

the total VAR% shows 84.6% but it should be

70,5% because the correct total sales is 35,250

settu_periasamy
Master III
Master III

what about your expression? have you tried like

Sum(TOTAL ActualSales)/Sum(Budget)

sunny_talwar

May be try this:

Sum(Aggr(YourExpression4TotalSales, YourTableDimensions)/YourExpression4Budget

hildacgg
Creator
Creator
Author

Thank you for your answer, still it is not working

I try using Aggr:

=Sum(Aggr(Sum({<[Cost Type]=,[Calendar Week]=,[Fiscal Period]=,[Fiscal Year]={2015},[Fiscal Quarter]={'Q1'}>}[Invoice Quantity] * [Cost Conversion]),[Part Number]))

But when I add the condition to remove part numbers with sales but no budget I get zeros for all records:

=Sum(Aggr(if(([Total (LBS)]>0),Sum({<[Cost Type]=,[Calendar Week]=,[Fiscal Period]=,[Fiscal Year]={2015},[Fiscal Quarter]={'Q1'}>}[Invoice Quantity] * [Cost Conversion]),0),[Part Number]))