QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for
Did you mean:
Creator II

Even Dollar Amounts

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

Labels (3)

• Rounding value Issue

1 Solution

Accepted Solutions
Master

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]

16 Replies
Master

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

Creator II
Author

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.......

Master

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]

Master

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

Master

where floor(POValue/1000)*1000 = POValue

Creator II
Author

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?

Creator II
Author

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

Master

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

Partner - Creator II

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

Community Browser