I have an Aged Receivables dashboard which shows credit limit, total invoice balance, % credit limit, current, over 30 60 90 and 120 days and future. This works well as it is.
However, we have some customers who have a parent child relationship, so we have the credit limit for the parent and 0 for the child as the child can utilise all of the parent’s credit. They have slightly different Cust ID and Names, but the DUNS is the same.
What I am trying to do is where there is a parent child, show the percentage credit limit of the child against the parents limit and have an overall total of the parent child and percentage credit limit. I do have 2 columns showing - one for the credit limit and the other for the parent credit limit.
I think my problem is relating to the £0K as things do not like being divided by 0, so have been trying to work out how to combine the 2 columns to have a total credit limit of £300k.
Hope this makes sense. It seems clear to me until I start typing.
Sounds like you could use a calculated field with a Case statement. When I have a field where a zero is possible, I will use something like “if DataFieldX <> 0 then {calc field calculation}, else 0”, meaning if the credit limit is not zero do the dividing calculation, otherwise make the field zero.
Thanks Jeff. Currently, I use the Customer.CreditLimit field as it is and then have a calculated field for the ParentCredit which is -
‘’‘case
when RlsHead.TopCustNum = Customer.CustNum then 0
else ParentCredit.Customer1_CreditLimit
end’‘’
If I then try to sum the 2 fields for a TotalCredit- ‘’‘sum(ParentCredit+Customer.CreditLimit)’‘’, it won’t work as it says Column 'Erp.Customer.CustID is invalid in the select list because it is not contained in either an aggregate function of the GROUP BY clause. I know I don’t want to group on this as they are different Cust IDs.
If I try to do the case function, I am getting a load of syntax errors, though I am probably doing something wrong here.
‘’‘case
if ParentCredit <> 0 then ((Calc_Total / Customer1.CreditLimit) * 100)
else 0
end’‘’
I reconstructed the baq to use just one aging subquery with case statements using datediff and if I read the parent percentage correctly I think I have that. Yes it looks odd with all of the group bys, but that is what makes sql happy and at least for my customers it runs 3X faster now.
I don’t have any DocTypes, so you will have to reset the radio buttons.
Thanks so much for this Greg, it works perfectly and is much quicker to run. I just have one other question, my Finance colleagues have decided that they only want to show the parent and child accounts on a single line with all the correct totals, instead of the current 2 lines.
The Cust ID is totally different, the Name fields are slightly different as they just have some extra letters at the end - ’ - CHILD’, so I don’t believe I can group by these. But I do have a customised BRN field which is like a registration number that is the same for the parent and child. Any ideas?
In the aging subquery you can make a custom field of the parent to group on, but doing this will always hide the child accounts and make the child percentage of the parent credit line meaningless.
Not knowing you data this baq is more conceptual than working. I made a Parent Aging using RlsHead joined to InvcHead and I used that joined to customer to filter only Parent customer records. If you have some customers that do not have a Rlshead record then this will get more complex with an outer join for both agings.
If that is the case I would make all of the aging fields calculated and then use ISNULL or Coalesce to show the correct one. Then in the dashboard you could set a filter to hide any child records so to the user the data presented was only parent customers or single customers.
