# Dimension wise total

Hi I have used below mention formula in expression but I don't know due to some reason I am not getting  dimension wise total of expression.

=(If(DIVISION ='L',-Sum(GROSSWT),
If(DIVISION ='D',-Sum(PCS),
If(DIVISION ='M',-Sum(PCS

)))))

Any idea why ?

• ###### Re: Dimension wise total

Hi.

Your formula is correctly defined for each row DIVISION.

On the total level it can't be calculated 'cuase there are more than one different values behind DIVISION statement.

The only way to calculate it for total is to sum it over DIVISION, for example like this:

=Sum(aggr(If(DIVISION ='L',-Sum(GROSSWT),
If(DIVISION ='D',-Sum(PCS),
If(DIVISION ='M',-Sum(PCS

))))), DIVISION))

• ###### Re: Dimension wise total

The problem is that you dont have a single DIVISION on the full aggregate level.

If you use straight table you can set the Total Mode to Sum of Rows.

IF not:

`=-Sum({<DIVISION={'L'}>}GROSSWT) - Sum({<DIVISION={'D','M'}>}PCS)`

Best regards

Vegar Lie Arntsen

QlikView consultant at egbs consulting ab

Blog (in Swedish): bi-effekten.se

• ###### Re: Dimension wise total

u use the ( before if statement it not needed then  everything is ok

now check this

=If(DIVISION ='L',-Sum(GROSSWT),
If(DIVISION ='D',-Sum(PCS),
If(DIVISION ='M',-Sum(PCS)

)))

• ###### Re: Dimension wise total

Hi Tushar,

Sorry I Didnt Get You Can You Please Send Me your Test App.

Regards,

Raj.

• ###### Re: Dimension wise total

hi

u use ( this bracket before if statement that is problem remove that

just replace with this script in ur expression

=If(DIVISION ='L',-Sum(GROSSWT),
If(DIVISION ='D',-Sum(PCS),
If(DIVISION ='M',-Sum(PCS)

)))

below im tried this

if(empno=1,-sum(sal),

if(empno=2,-sum(sal),

if(empno=3,-sum(sal+1),

If(empno=4,44)

)))

• ###### Re: Dimension wise total

Sure!

Here u go.

Best regards

Vegar Lie Arntsen

QlikView consultant at egbs consulting ab

Blog (in Swedish): bi-effekten.se

• ###### Re: Dimension wise total

Hi

At the total line, QV cannot determine a single DIVISION, so all your if statements return false. You have two options:

• If this is in a straight table, you can enable sum of rows in the Total Mode in Properties | Expressions. This option is not available for pivot tables.
• Use an Aggr() expression

Sum(Aggr(If(DIVISION ='L',-Sum(GROSSWT),

If(DIVISION ='D',-Sum(PCS),

If(DIVISION ='M',-Sum(PCS

)))), dimension1, dimension2, dimension3))

I dont know what dimensions are on your table, so replace dimension1, 2 etc with the fields are dimensions on your table. You need as many field names here as you have dimensions.

Hope that helps

Jonathan

• ###### Re: Dimension wise total

Use aggr function

Please find attached the QV application

Regards

Mhatim

• ###### Re: Dimension wise total

-sum(if(DIVISION='D' or DIVISION='M' , PCS,GROSSWT))

Thanks I used above mention formula finally.