Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
-sum(if(DIVISION='D' or DIVISION='M' , PCS,GROSSWT))
Thanks I used above mention formula finally.
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))
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
QlikView consultant at egbs consulting ab
Blog (in Swedish): bi-effekten.se
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)
)))
Hi Tushar,
Sorry I Didnt Get You Can You Please Send Me your Test App.
Regards,
Raj.
Hi
At the total line, QV cannot determine a single DIVISION, so all your if statements return false. You have two options:
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
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)
)))
Sure!
Here u go.
Best regards
QlikView consultant at egbs consulting ab
Blog (in Swedish): bi-effekten.se
Use aggr function
Please find attached the QV application
Regards
Mhatim
-sum(if(DIVISION='D' or DIVISION='M' , PCS,GROSSWT))
Thanks I used above mention formula finally.