Sum based on condition

abhaykumarmehta
abhaykumarmehta
in Modeling

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

Tagged:

Best Answer

  • anikdas
    anikdas
    Answer ✓

    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

Answers

  • abhi1017
    abhi1017

    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

     

     

  • abhaykumarmehta
    abhaykumarmehta

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

Categories