4 Replies Latest reply on Aug 17, 2016 7:19 AM by Andres Galban

LOD vs If-Statement (or other?)

First post . Having some trouble with dimensionality, hope some of you can help . I have the following scenario:

Column A denotes the invoice total.

Column B denotes each line item within the invoice. (one to many to Column A)

Column C denotes the employee who made the sale. (One employee - To many invoices)

Rule: If the Invoice Total is less than a particular amount (lets say \$1000), a special fee needs to appear as an Invoice Line Item (Column B). The required amount of \$1000 is not including the special fee.

#1 First I need to subtract the special fee (if it exists) from the Invoice Total

#2 Then filter and count the number of Invoices that are less than \$1000 (per employee)

#3 Then count the number of special fees applied to these invoices (per employee)

#4 Lastly divide #3 / #2 to arrive at the compliance percentage (per employee).

• 1. Re: LOD vs If-Statement (or other?)

Post a twbx or an xls file with data and your need please.

• 2. Re: LOD vs If-Statement (or other?)

Luciano,

I've attached a mock up. Thanks

• 3. Re: LOD vs If-Statement (or other?)

Hi Andres,

I took a crack at it and I've attached a workbook. I broke out the calcs to keep it simple but you could of course combine them.

Let me know if this is what you are looking for!

Take care,

Keshia

1 of 1 people found this helpful
• 4. Re: LOD vs If-Statement (or other?)

Thanks Keisha! This is great. It satisfied the requirement almost 100%  , What i noticed was that the "Special Fee Charged" count takes into consideration all Invoices , when it should only consider those less than \$1000.  Following the example, if there is an invoice with a final amount of 2500 that includes a special fee, this special fee should not be counted towards the compliance percentage.

Regardless, this takes me almost all the way. Thanks for the help!

EDIT:

I added the following criteria (levering the calc you had already built) to the Special Fee Charged measure and it is now 100% correct. Thanks again :]

{FIXED [Invoice] :

IF SUM([Total Amount (No Special Fees)]) < 1000 THEN

MAX(IF [Line Item] = "Special Fee" THEN 1 ELSE 0 END) END }