Even Dollar Amounts

[gfisch13]

Good morning folks - I'm working with a file of purchase orders and want to identify only those that have even amounts, for instance, $10,000, $50,000, $1,250,000, $6,000,000.   I've searched these boards but have yet to hit on what I'm looking for.   Is there an easy way to do this.  In other applications I've seen this called Round Amounts or Flat Dollar Amounts.  Just not sure what to call it here.  

Appreciate the guidance.  Thanks!

George

[edwin]

you can use round function:

round(11700,1000) is 12000 (rounded off to the nearest 1000.  set the step value if you wanted it by 1000, 10000, etc..

Round ‒ QlikView

[gfisch13]

This is close, but not exactly what I was looking for.   See the figure attached.  I'm not actually looking to do any rounding, I want to select the PO's that are at certain thresholds.  In this figure I want to be able to look at the ones highlighted in yellow.   I'm thinking I need to do this in the script.......

[edwin · Accepted solution]

What would be the business rules in selecting some over the others?  why is 5002510 not selected?  is it because it is not in 1000s?  you can use round function this way:

where round([PO Value],1000) = [PO Value]

[edwin]

just change the step depending on what your business rule is (1000, 10000, etc)

[edwin]

or a more readable manner:
where floor(POValue/1000)*1000 = POValue

[gfisch13]

Thank you Edwin - appreciate the responses.  I'm doing some fraud testing and we are trying to detect PO's where the amount is a flat amount as I showed since those can have a higher risk of being repetitive and sometimes users can break a PO into pieces to circumvent approvals/hide spend.

As I see you are using the 'where', isn't that a statement that would be reserved for and SQL load?

[gfisch13]

Thanks Edwin - I removed the where from the script and it worked perfectly.  Exactly what I was looking for!!

[edwin]

i added the where clause as i was expecting you would use it in the script as a filter, regardless, the principle is the same if you use it as an if statement in a field field.  doing this kind of determination in the script makes for a faster UI 

[vidyut]

You could also use substringcount(text(POValue),`0`) to get number of zeros in the POValue field, that might be interesting feature.