Sum based on condition
Request guidance on how SUM can be applied based on condition:
Get average difference between invoice and receipt date by customer for Cleared Lines only
1. Customer (line by line) transactional data stored in a flat (non-dimensional 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]
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,