Aggregation Question

kevinstanfield — Mon, 14 Sep 2015 09:24:54 GMT

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

topic Aggregation Question in QlikView

Aggregation Question