Sum based on condition
Hi,
Request guidance on how SUM can be applied based on condition:
Objective:
Get average difference between invoice and receipt date by customer for Cleared Lines only
Model structure:
1. Customer (line by line) transactional data stored in a flat (nondimensional file)
2. Customer lines can have either "cleared" or "open" status dynamically determined based on imported data
3. Cleared? (Boolean) : IS NOT BLANK "Clearing Date"
4. Credit days (Number) : If Cleared? is TRUE, "Invoice Date" MINUS "Receipt Date" ELSE 0
5. Average Credit Days (Number): Credit days [AVERAGE: by Customer]
Issue:
On step 4, the difference is calculated for both cleared and open item (i.e. 0)
On step 5, unable to AVERAGE only for Cleared? = TRUE, the average considers for both cleared items and 0 for open items as well and hence is incorrect. It should be calculated for Cleared Lines only.
Screenshots attached for your reference. In Screenshot 3, the average is 7.65 which should actually be 10.35 if only cleared lines are considered.
Thanks for your help,
AM
Best Answer

Understood the problem that you are facing. Indeed, it takes 0 as well while calculating average thus bringing the average down. In this case, using "Average" function will not give you the correct value. I would recommend following this approach:
 Calculate the difference of days like you are doing
 Create another line item (or convert the line item called "Cleared?" into a number formatted) and count as 1 against the transactions where it is cleared
 Sum up the difference of days and count at customer level
 Divide the total difference by total count to come to the right average
Hope this helps.
Thanks
Anik
0
Comments

Hi Abhay,
Step 4 : Could you pls share the screenshots.
Step 5:Just add a condition ahead in the formula
If cleared ? then CAL 1.1 Customer..............(the formula already there) else 0
But prior to adding this condition, check dimensions of the modules (source and target).
Or If you could share the dimensions of the source module where Cleared ? is checked, Solution can be provided.
Hope it helps.
~Abhi
0 
@anikdas thanks, converting boolean to 0 and 1 and then applying SUM works
0