Formula does not work

[Daniel1908]

Hello Together, 

The below formula doese not work.
Can you help?

=Count({<[Price-3]-={[Price-1]+[Price-2]},[ReturnLabel]={'A*'}>})Material&Shorttext)

Count all Price-3 which do not match with the total of Price-1 + Price-2 and have Return Label beginning wich A*.

Kind Regards, 
Daniel

[Fernando_Fabregas]

Hi Daniel.

For searches you must use double quotes:

   [ReturnLabel]={"A*"}

Full explanation here: 

https://help.qlik.com/en-US/sense/February2021/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/SetAn...

Regards, Fernando

[Daniel1908]

Hi Fernando, 

Thanks for the reply.
Unfortunately the formula does still not work.

Do you have any ideas?

Kind Regards
Daniel

[Fernando_Fabregas]

Hi Daniel. Try this: 

Count({<[ReturnLabel]={'A*'}>})   if([Price-3]<>{[Price-1]+[Price-2], Material&Shorttext ) )

I think this way it will work fine, but you won't get the best performance because if () evaluates each row in the table one by one.

Regards.

[MayilVahanan]

Hi @Daniel1908 

Try like below, bring flag field in script

Load *, if([Price-3]<>{[Price-1]+[Price-2], 1, 0) as Flag from ursourcetable;

In front end, try like below

=Count({<Flag ={1},[ReturnLabel]={"A*"}>})Material&Shorttext)

[Daniel1908]

It shows Error in expression. Count takes 1 parameter. 

[Daniel1908]

Formula now shows OK after taking away the bracket before "if".

But does not show any values. 

Kind Regards

[Daniel1908]

Hi Mayil, 

it seems to work. 

Do you know how to exclude the differnce of the prices at the decimal places?

Kind Regards,
Daniel

[MayilVahanan]

HI @Daniel1908 

might be Try like below

Load *, if([Price-3]-([Price-1]+[Price-2]) > 1, 1, 0) as Flag from ursourcetable;

[Daniel1908]

Hi Mayil, 

unfortunately I just get 0 Values, but the formula shows still OK. 

if("Price-3"-"Price-1"+"Price-2",'>1''1','0') as Flag,  (from ursourcetable)

=Count({<Flag ={1},[ReturnLabel]={"A*"}>})Material&Shorttext)

Any Idea?

Kind Regards,
Daniel