topic Re: Sum Total - Maybe with Aggregate in QlikView

Sum Total - Maybe with Aggregate

silvia_assuncao — Mon, 26 Jun 2017 17:45:07 GMT

Hi All!

I have The total value within the lines are correct, but the total sum is wrong.

anyone know what change I need in the expression to have the right Total Sum?

My current expression is: Sum(DISTINCT(if(Month=Month_Working_Days,Working_Days_Qt*8)))

Re: Sum Total - Maybe with Aggregate

sunny_talwar — Mon, 26 Jun 2017 17:49:42 GMT

May be this

Sum(Aggr(Sum(DISTINCT(if(Month=Month_Working_Days,Working_Days_Qt*8))), Resource, [Project Name], [Business Area], Month))

Re: Sum Total - Maybe with Aggregate

sunny_talwar — Mon, 26 Jun 2017 17:56:06 GMT

Or this

If(Dimensionality() = 0, Sum(Aggr(Sum(DISTINCT(if(Month=Month_Working_Days,Working_Days_Qt*8))), Resource, Month)), Sum(DISTINCT(if(Month=Month_Working_Days,Working_Days_Qt*8))))

Re: Sum Total - Maybe with Aggregate

silvia_assuncao — Mon, 26 Jun 2017 18:18:03 GMT

Last one:

There is a conditional before and then the subtraction:

if(Sum([Hours Resource])/
Sum(DISTINCT(if(Month=Month_Working_Days,Working_Days_Qt*8)))
<=.99,

Sum(Aggr(Sum(DISTINCT(if(Month=Month_Working_Days,Working_Days_Qt*8)))
-Sum([Hours Resource]),Resource,Month, [Resources Location])))

Re: Sum Total - Maybe with Aggregate

silvia_assuncao — Mon, 26 Jun 2017 18:18:42 GMT

What can I do to show the totals?

Re: Sum Total - Maybe with Aggregate

sunny_talwar — Mon, 26 Jun 2017 18:26:23 GMT

May be this

Sum(Aggr(if(Sum([Hours Resource])/

Sum(DISTINCT(if(Month=Month_Working_Days,Working_Days_Qt*8)))

<=.99,

Sum(Aggr(Sum(DISTINCT(if(Month=Month_Working_Days,Working_Days_Qt*8)))

-Sum([Hours Resource]),Resource,Month, [Resources Location]))),Resource,Month, [Resources Location]))

Re: Sum Total - Maybe with Aggregate

silvia_assuncao — Mon, 26 Jun 2017 18:57:31 GMT

Totally worked! thanks a lot!