Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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 ?

1 Solution

Accepted Solutions
Not applicable
Author

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

Thanks I used above mention formula finally.

View solution in original post

9 Replies
whiteline
Master II
Master II

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

Vegar
MVP
MVP

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:

I don't know your dataset, but alternativiely try using SET-analysis instead:

=-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

Not applicable
Author

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)

)))

Not applicable
Author

Hi Tushar,

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

Regards,

Raj.

jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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)

      )))

Vegar
MVP
MVP

Sure!

Here u go.

Best regards

Vegar Lie Arntsen

QlikView consultant at egbs consulting ab

Blog (in Swedish): bi-effekten.se

Not applicable
Author

Use aggr function

Please find attached the QV application

Regards

Mhatim

Not applicable
Author

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

Thanks I used above mention formula finally.