topic Dimension wise total in QlikView

Dimension wise total

Thu, 01 Nov 2012 20:30:42 GMT

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

whiteline — Fri, 02 Nov 2012 08:34:49 GMT

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

Vegar — Fri, 02 Nov 2012 09:02:25 GMT

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

Re: Dimension wise total

Fri, 02 Nov 2012 09:02:52 GMT

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

Fri, 02 Nov 2012 09:36:11 GMT

Hi Tushar,

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

Regards,

Raj.

Re: Dimension wise total

jonathandienst — Fri, 02 Nov 2012 09:45:25 GMT

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

Fri, 02 Nov 2012 09:46:31 GMT

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

Vegar — Fri, 02 Nov 2012 09:59:42 GMT

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

Fri, 02 Nov 2012 10:08:32 GMT

Use aggr function

Please find attached the QV application

Regards

Mhatim

Re: Dimension wise total

Fri, 16 Nov 2012 17:40:40 GMT

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

Thanks I used above mention formula finally.