0 Replies Latest reply: Sep 14, 2015 5:24 AM by Kevin Stanfield RSS

    Aggregation Question

    Kevin Stanfield

        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