19 Replies Latest reply: Nov 22, 2011 12:57 PM by rizo98 RSS

    same dimension twice

      Is the best/easiest/quickest way to use the same dimension twice is to create the same table again?

        • Re: same dimension twice
          Stefan Wühl

          rizo98,

           

          I am not sure if this is the optimal way, but if you are looking for something like attached, at least I do it this way pretty often.

          You don't need to copy the complete table, just the fields you need.

           

          Hope this helps,

          Stefan

            • Re: same dimension twice

              I cant open the qv file as I only have the QV personal edition.

              Would you be able to post the script?

               

              But I do understand the idea of only copying the fields I need.

                • Re: same dimension twice
                  Stefan Wühl

                  Sure, quite a simple demo:

                   

                  Transactions:

                  LOAD

                  recno() as TransID,

                  ceil(RAND()*100) as PersonID,

                  chr(floor(RAND()*10+65)) as Category

                  AutoGenerate 150;

                   

                  LOAD

                  PersonID,

                  Category as Category2

                  resident Transactions;

                   

                  Then I created a grid chart with dimensions Category and Category2 (note that you can re-label to have the same Name displayed), and expression

                  =if(Category<=Category2,count( distinct PersonID))

                   

                  Regards,

                  Stefan

                    • Re: same dimension twice

                      My problem is slightly different.

                      you will recall this thread.

                      http://community.qlik.com/message/166041#166041

                       

                      I am trying to create a matrix table.

                      as shown in attached excel file

                      the QV is also attached.

                        • Re: same dimension twice
                          Celambarasan Adhimulam

                          I couldn't get your problem exactly but check with this,

                          Load the same table twice by changing the field names and the used the required fields  from the two table as dimension.
                          It will mostly a cross join.

                           

                          Regards,

                          Celambarasan

                          • Re: same dimension twice
                            Stefan Wühl

                            Ok, I slightly remember our last yesterday's discussion. How do you read this table?

                             

                            I assume rev is revenue and has something to do with Salesman ID, and Salesman ID1 determines the RevForDay, right?

                              • Re: same dimension twice

                                I want the table to display this:

                                Display the individual sales totals for the days my colleagues and I have worked together

                                so e.g. 2006 has worked with 2005 for 5 days and during these 5 days, he has made sales of 45000, then this should be displayed in the table/pivot along with other indivuals who have also worked on the day(s).

                                I hope i'm clear

                                  • Re: same dimension twice
                                    Stefan Wühl

                                    Well, I get different numbers, in your example 79375 for those 5 days. If this is correct, try

                                    =sum( aggr(if(sum([List Price]), sum(total<[Salesman ID],Day> [List Price])), [Salesman ID1],[Salesman ID],Day))

                                     

                                    Stefan

                                      • Re: same dimension twice

                                        Stefan,

                                        79375 is the total for everybody together.

                                        I would like totals by individual.

                                        i hope i'm not putting you into too much bother

                                        thanks

                                          • Re: same dimension twice
                                            Stefan Wühl

                                            Are you sure?

                                             

                                            this is what I get for the 5 days for CustomerID 2006:

                                             

                                            Salesman ID Day sum([List Price])


                                            79375
                                            2006 1 4599
                                            2006 4 4599
                                            2006 7 1299
                                            2006 9 41391
                                            2006 11 27487
                                              • Re: same dimension twice

                                                sorry, this is probably doing your head in

                                                All I want to show is the total of salesman sales against other salesman for the number of days they have worked together.

                                                I dont need to show the days which they have worked together, just the total

                                                  • Re: same dimension twice

                                                    So in conclusion I should get a table like below. (figures are not accurate)

                                                     

                                                     

                                                    SalesmanASalesmanBSalesman A sales compared to SalesmanB
                                                    Salesman2005Salesman200565.00
                                                    Salesman2005Salesman20074500.00
                                                    Salesman2005Salesman2008187.00
                                                    Salesman2006Salesman2006415.00
                                                    Salesman2006Salesman2007450.00
                                                    Salesman2006Salesman2008441.00
                                                    Salesman2006Salesman2009400.00
                                                    Salesman2007Salesman200565.00
                                                    Salesman2007Salesman2006400.00
                                                    Salesman2007Salesman20075197.00
                                                    Salesman2007Salesman2008906.00
                                                    Salesman2007Salesman2009652.00
                                                    Salesman2008Salesman200565.00
                                                    Salesman2008Salesman2006415.00
                                                    Salesman2008Salesman20075197.00
                                                    Salesman2008Salesman20088042.50
                                                    Salesman2008Salesman2009652.00
                                                    Salesman2009Salesman2006400.00
                                                    Salesman2009Salesman2007697.00
                                                    Salesman2009Salesman2008719.00
                                                    Salesman2009Salesman2009652.00
                                                    • Re: same dimension twice
                                                      Stefan Wühl

                                                      sorry, this is probably doing your head in

                                                      All I want to show is the total of salesman sales against other salesman for the number of days they have worked together.

                                                      I dont need to show the days which they have worked together, just the total

                                                       

                                                      I think I understand. I just wanted to ask you to recheck the total sum under question. For the common days that Salesman2005 and Salesman2006 have worked together, I get days 1,4,7,9,11. The above table is only to demonstrate that Salesman 2006 alone has a total sales for those days of 79375.

                                                      You said this is the value for all Salesman, and I was not able to confirm that.

                                                        • Re: same dimension twice

                                                          Stefan,

                                                          "I think I understand. I just wanted to ask you to recheck the total sum under question. For the common days that Salesman2005 and Salesman2006 have worked together, I get days 1,4,7,9,11. The above table is only to demonstrate that Salesman 2006 alone has a total sales for those days of 79375."

                                                          the above is absolutely correct.


                                                          so Salesman2006 will have 79375 against Salesman2005.

                                                          Salesman2005 will also have his 60181 in same table (table format above)

                                                            • Re: same dimension twice
                                                              Stefan Wühl

                                                              Ok, but these are the numbers that my above posted expression

                                                               

                                                              =sum( aggr(if(sum([List Price]), sum(total<[Salesman ID],Day> [List Price])), [Salesman ID1],[Salesman ID],Day))

                                                               

                                                              is giving, aren't they?

                                                               

                                                              Please have a look at attached sample.

                                                               

                                                              Regards,

                                                              Stefan

                                                                • Re: same dimension twice

                                                                  Stefan,

                                                                  I have QV personal edition so I cannot opeb the file.

                                                                   

                                                                  =sum( aggr(if(sum([List Price]), sum(total<[Salesman ID],Day> [List Price])), [Salesman ID1],[Salesman ID],Day))

                                                                  This does the tirck. Thank you very much.

                                                                  But now I cant get the total of Rev in table Rev to display next to A-B in the attached sheet.

                                                                    • Re: same dimension twice
                                                                      Stefan Wühl

                                                                      That was a bit tricky, please try

                                                                       

                                                                      = num(sum(total<[Salesman ID]> aggr( if(sum({1}[List Price]), sum(total<Day> {1}[List Price])), [Salesman ID],Day,[Salesman ID])),'##0')

                                                                       

                                                                      to get the Rev column in your table with both Salesman ID and Salesman ID1.

                                                                       

                                                                      Hope this correct,

                                                                      Stefan

                                                                       

                                                                      edit:

                                                                      a

                                                                      = num(sum(total<[Salesman ID]> aggr( if(sum({1}[List Price]), sum(total<Day> {1}[List Price])), [Salesman ID],Day)),'##0')

                                                                       

                                                                      should be enough, there was an additional Salesman ID dimension in aggr by mistake.

                                                                       

                                                                       

                                          • Re: same dimension twice
                                            Celambarasan Adhimulam

                                            This will work.

                                             

                                            For first dimension select the field normally.

                                            For second dimension add calculated dimension as =fieldname.

                                             

                                            Regards

                                            Celambarasan