10 Replies Latest reply: Oct 31, 2011 10:26 AM by Kiran Rokkam RSS

    New User needing an epiphany!

      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!

       

      QlikViewQuery.bmp

        • New User needing an epiphany!

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

           

          Kiran.

            • New User needing an epiphany!

              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!

                • Re: New User needing an epiphany!

                  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-)

                    • New User needing an epiphany!

                      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.

                        • Re: New User needing an epiphany!

                          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-)