Hi
I’ve previously raised a question in regard to a data set I’m using see (https://community.qlik.com/message/829750?et=watches.email.thread#829750)
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.
Thanks in advance.
Kevin
