Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

kevinstanfield
New Contributor III

Aggregation Question

  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

 

Community Browser