nested IF with AND

[TomBond77]

Hi experts

What's wrong with this formula?

=IF([Stock Type] <> 'Quality Inspection', Sum([Total Stock]), IF([Stock Type] = 'Quality Inspection' AND [Movement Type] = '', Sum[Total Stock]))

Many thanks for your help

Tom

[Anil_Babu_Samineni]

You missed one parenthesic open and close like

=IF([Stock Type] <> 'Quality Inspection', Sum([Total Stock]), IF([Stock Type] = 'Quality Inspection' AND [Movement Type] = '', Sum([Total Stock])))

[martinpohl]

Hello,

this kind of expression only works in one line, not in total.

For that you have to define on what dimensions this expression should be used and an aggregation.

But:

what do you want to achieve?

The first if -statement is part of the second one so you will never get a result for second part...

[TomBond77]

Hi Martin

Meanwhile it changed, I need to define this rule: =IF([Movement Type] = '', Sum([Total Stock]))

The field "Movement Type" is not on the line, only the field "Stock Type". Total Stock should be calculated on stock type based on movement = ''.

Any ideas?

Thanks

Tom

[martinpohl]

the syntax in Qlik is

sum{<[Movement Type]={''}>} [Total Stock])

but I'm not sure if it works with '' (not filled)

[TomBond77]

Thanks Martin, unfortunately it is not working...

[martinpohl]

one type mistake by me

sum({<[Movement Type]={''}>} [Total Stock])

[TomBond77]

thanks Martin, still not working.

But I found the solution:

=if([Movement Type]= '', Sum([Total Stock]))