Sum based on condition

Highlighted
New Contributor

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 (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]

 

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

3 REPLIES 3
Highlighted
Frequent Contributor

Re: Sum based on condition

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

 

 

Highlighted
Certified Master Anaplanner

Re: Sum based on condition

Hi @abhaykumarmehta 

 

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

Thanks
Anik
Highlighted
New Contributor

Re: Sum based on condition

@anikdas thanks, converting boolean to 0 and 1 and then applying SUM works