New User needing an epiphany!

[#1]

Hello Community!

This is my first post... perhaps of many!

I am a new user of QlikView and it is my desire to introduce the software as the BI standard for the company I work for.

I have encountered my first problem that I am struggling to get to grips with.

I believe set analysis is the solution, but I cannot quite get my head around the logic.

Please see the attached image.

The Black text is an extract of data (a single invoice) showing line detail.

Our product range (paint) includes items which are supplied as multipart sets, although each part is invoiced separately on the face of the invoice.

In such cases, I would like to combine the data to give mixed totals.

The route I have chosen to take is a Straight Table, allocating the catalysts to the colours where appropriate.

I then intend to calculate line totals within the table and hide any extraneous information.

(The reason being that this will also be issued in list format as a report.)

I have simply created line sums initially, but then taken this to the next stage by creating sums for Col types only (A Totals in purple) and Cat types only (B Totals in red).

But what I really need is to replace the red section with what I have shown (using Excel) in blue.

This is where I need help if you are able.

I hope that set analysis is the answer, but if we have to delve into script, well I'll take that as an option.

I also hope I have given a clear enough explanation of my problem - but if you need anymore details, please leave a reply and I'll try to fill in any gaps.

We are using QlikView for Windows Version 9.00.7646.9 SR6

Many Thanks!

[#2]

Missed one more thing, changed the total mode from "Expression Total" to "sum of rows" for both the mixed functions.

Kiran.