topic Re: Totals in columns in QlikView

Totals in columns

hildacgg — Tue, 22 Sep 2015 01:30:26 GMT

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.

Re: Totals in columns

settu_periasamy — Tue, 22 Sep 2015 01:52:06 GMT

Hi,

Can you post the sample and expected output?

Re: Totals in columns

hildacgg — Tue, 22 Sep 2015 02:18:07 GMT

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

Re: Totals in columns

settu_periasamy — Tue, 22 Sep 2015 02:42:18 GMT

what about your expression? have you tried like

Sum(TOTAL ActualSales)/Sum(Budget)

Re: Totals in columns

sunny_talwar — Tue, 22 Sep 2015 03:27:56 GMT

May be try this:

Sum(Aggr(YourExpression4TotalSales, YourTableDimensions)/YourExpression4Budget

Re: Totals in columns

hildacgg — Tue, 22 Sep 2015 14:19:29 GMT

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