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]

if(Not isNull(Catalyst),sum(qty)) will do the job.

Kiran.

[#3]

Of course it does, Kiran... I was overcomplicating things.

Just need to get my head round the Functions now to get the pro rate volumes and values!

Thanks for the kickstart... much appreciated!

[#4]

Hello again all,

I've spent more than a day trying to figure this out, but I need help calculating the last two colums of the Red area in my example above.

I thought I would be able to do this, but I keep hitting brick walls.

Just to be clear, the Catalyst total qty will (should) always equal the Colour total qty. (A Colour to A Catalyst etc.) but I need to spread the Volumes and Values pro rata to the quantity - invoice by invoice.

I can make QV select the set of ALL current data or just the set of current line data, but I cannot make it choose the invoice data.

Please be aware, this needs to be a generic solution.

I will continue to bash away at it, but if any of you kind people can give me at kick in the right direction I would, of course, be grateful for all assistance received before my head explodes!

8-)

[#5]

Hi Dave,

Good that you could get the first part cleared out. But spreading the volumes and values, I didnt understand. In your image A.Val + B.Val = Color Val, which means you already spread it invoice by invoice. Ofcourse formulation for Prorata to the quantity is to be defined.

As I understand you need to group the Volume and Value by invoice. If thats what you are looking for try using TOTAL keyword which is analogus to group by in SQL.

Hope this helps,

Kiran.

[#6]

Hi again, Kiran,

The problem I am having is that the source data does not match Cat and Col.

I have to create (and where necessary, maintain) a table within the QV datasource (an MS Access Database) to "match" catalysts to colours.

Once done, I need to report "mixed" totals, ignoring the underlying data structure.

I chose to do this in a straight table as this most resembles both a spreadsheet, and the current format of document issued to our sales teams (this cannot perform the prorata calculation either... same issues).

I've tried using AGGR, which seems to almost get me there, but I think I need to come up with some novel combination of AGGR and Set Analysis.

I'm hoping it's not that novel actually, and that someone in the community has already resolved this problem.

However, I am also wondering if I need to be using a pivot table rather then straight. Any opinion?

The real frustration is that I know I could do this in Access in about 30 miuntes flat... but that rather defeats the point.

I'm sure there must be a QV solution!

Ah well... back to the coal-face.

8-)

[#7]

Dave,

Can you post your application (or sample of that) here? If not possible atleast try to share snapshots/excel of your QV data model and desired result. I will try to help you.

Kiran.

[#8]

Hi yet again, Kiran,

Really appeciate your efforts here.

I don't know how to do the former, so please find a excel file attached.

Hope this gives you what you asked for!

Cheers

Dave

[#9 · Accepted solution]

Phew, that was a bit complex to understand. Enclosed the required output and matched it with excel.

This is what I did:

In script created three fields. Computed as requested with set analysis and TOTAL keyword. Since the Mixed expressions are bit complex I broke them down to three more expressions and disabled them (for you to understand if needed.

Hope this helps you,

Kiran

[#10]

Wow! Many thanks for your efforts, Kiran,

I think I get most of that, but I'll invest some time deconstructing and learning!

Thanks.