topic Re: Sub Totals not equalling in QlikView

Sub Totals not equalling

Tue, 11 Aug 2015 08:28:09 GMT

I've added an expression in my table that returns a zero based on the condition and it seems to be working ok but for some reason the column total is still including the value of the records that return zero

So I'm returning zero when ActualCostsLC - SalesActualCostslC = 0 otherwise I'm bringing in the sum of PlannedCostsLCV0.

if(sum(ActualCostsLC)- sum(SalesActualCostsLC) = 0 , 0, sum(PlannedCostsLCV0))

The report returns zero for the condition but when I add a subtotal I still get the values in the total that have been excluded

Re: Sub Totals not equalling

sunny_talwar — Tue, 11 Aug 2015 08:30:02 GMT

Try this:

=Sum(Aggr(if(sum(ActualCostsLC)- sum(SalesActualCostsLC) = 0 , 0, sum(PlannedCostsLCV0)), YourDimensions))

Re: Sub Totals not equalling

Tue, 11 Aug 2015 08:43:11 GMT

Thanks but it didn't work, for some reason it bought in all of the values

Re: Sub Totals not equalling

sunny_talwar — Tue, 11 Aug 2015 08:46:07 GMT

That is strange, I would have expect the individual rows to have returned the same result, but only subtotal to add up each of the row. Are you trying to do this in a pivot table or Straight Table?

Re: Sub Totals not equalling

Tue, 11 Aug 2015 08:46:57 GMT

Sorry , the values were excluded but the subtotal was the same, for the dimension in the formula I'm brining in the dimension that I'm subtotalling on ?

Re: Sub Totals not equalling

Tue, 11 Aug 2015 08:49:32 GMT

so the individual rows are returning the same result but the total is still not working and its a pivot table

Re: Sub Totals not equalling

sunny_talwar — Tue, 11 Aug 2015 08:53:07 GMT

Add all the dimensions and see if that helps.

Re: Sub Totals not equalling

Tue, 11 Aug 2015 08:59:47 GMT

Thanks I've got it working now

Re: Sub Totals not equalling

sunny_talwar — Tue, 11 Aug 2015 09:01:03 GMT

Awesome

I am glad it worked out for you.

Best,

Sunny

Re: Sub Totals not equalling

Tue, 11 Aug 2015 09:04:54 GMT

Hi Thomas

PFA the qvw. It may help you

Regards

Chitra