I ’m now looking at different problem which is to calculate the amount of debt per customer for the most recent aged debt week - I’ve being using set analysis to identify the latest week
My issue is that the Customer can exist at one of 3 hierarchies with accounts attached to each level.
The simplest way to explain this is by example:
Group = Level 1
Corp = Level 2
Account = Level 3
Group A has Corp AA, AB & AC and AA, AB, AC each have many accounts and what I need to achieve is the total amount for Group A which in this case is £171,929
Group
Corp
Account
Amount
A
AA
123456
-£1,234
A
AA
246934
£24,566
A
AA
370412
£1,965
A
AA
493890
£19,653
A
AA
617368
£24,566
A
AA
740846
£1,965
A
AA
864324
£19,653
A
AA
987802
-£5,678
A
AA
1111280
£0
A
AA
1234758
£57
A
AA
1358236
£100
A
AA
1481714
£5,000
A
AB
1605192
-£59
A
AB
1728670
£15,000
A
AB
1852148
£11,250
A
AB
1975626
£16,500
A
AB
2099104
£15,000
A
AC
2222582
£11,250
A
AC
2346060
£16,500
A
AC
2469538
£12,375
A
AC
2593016
-£16,500
However there are also two other scenarios to cater for 1) where there is a Corp but not Group and 2) where there are just accounts – this could be multiple accounts in the same customer name or single accounts however the aggregation would be at “Customer Name” level.
So using descriptive terms I want, for the most recent aged debt date, to sum all accounts for a unique group if a group exists, if no group exists then sum all accounts for a unique Corp and if no group or corp exists then sum all accounts for each “customer name”.
I’m a pretty basic user and self taught so far so any help much appreciated.
