11 Replies Latest reply: Jan 23, 2013 5:03 PM by roger.luo RSS

    Help with the Average

      Hi everyone.

       

      I am trying to put the Average as a column (call it B) rather than a row in a pivot table. But the other expression (sales, volume) stay the same.

       

      This is the table I have now:

      Capture.PNG

       

       

       

       

      If it is possible, I would like my table look like

       

      Product
      XXX

      Average
      Cementsum(sales)126002520
      CementSum(volume)2106421.2
      Grandsum(sales)245004900

       

       

      Right now, the dimension is "Product", all the rest are expressions. May be the final table can be achieved somehow?

       

      Thank you very much for your help.

       

      Roger.

        • Re: Help with the Average

          Sorry. My table looks like follows.

          Capture.PNG

           

          Right now, I can only put the average one by one:

           

          Capture 1.PNG

           

           

          My desired table is

          Product
          XXX

          Average
          Cementsum(sales)126002520
          CementSum(volume)2106421.2
          Grandsum(sales)245004900

           

          Any ideas?

           

          Thank you.

            • Re: Help with the Average
              Deepak Vadithala

              Hi Roger,

               

              Assuming that you want something like attached? Just wanted to make sure...

               

              I shall mock up something as your desired table but just wanted to check if this works?

               

              Thanks,

              DV

               

              www.QlikShare.com

                • Re: Help with the Average

                  Hi Deepak.

                   

                  Thanks for your reply.

                   

                  It is not really what I wanted. I know you can drag dimensions and expressions in pivot table. But when I drag it, all the expressions (sum(sales), average, sum(volume) all moved together

                   

                  Capture.PNG

                   

                  But could you somehow move the average alone so that the table looks like my desired table?

                  Because I want to export to excel later. That's the format people want.

                   

                  Thanks.

                • Re: Help with the Average
                  Deepak Vadithala

                  Here is the updated version. Please check both Version 1 & 2 and use whatever suits your requirement. Although Synthetic Dimensions are very effiecient with large datasets. And there is lot of CPU overhead for Synthetic Dimensions compared to regular Dimensions.

                   

                  I hope this helps!

                   

                  Cheers,

                  DV

                   

                  www.QlikShare.com

                    • Re: Help with the Average

                      Hi DV.

                       

                      The Aggr function looks great. It almost achieves the desired table. I have checked both Version 1 & 2. It is very close to what I want. You use the following in calculated dimension:

                       

                      Aggr(sum(Sales)/Day, Product)

                       

                      But this will only give you the average of the Sales right. However, the average of the volume is not shown at the place I highlighted. If I use this aggr approach in the calculated dimension, the average for the sum(volume) is not correct.

                       

                      It would be very nice it the "Average" column can show the average of every individual expression.

                       

                      Capture.PNG

                       

                      Thank you very much.

                       

                      Roger.

                      • Re: Help with the Average

                        Hi DV.

                         

                        I wish in the presentation tab, there is a selection "partial average" or a field where you can add the expression, just like the "partial sum" we have right now.

                         

                        If I want the sum instead of calculated average, the selection of "partial sum" already does the work.

                         

                        Hope this helps to demonstrate what I expect.

                         

                        Thank you for your time.

                          • Re: Help with the Average
                            Deepak Vadithala

                            Hi Roger,

                             

                            My bad! I didn't do enough testing in my previous post. I have changed the method but I have realised that you wanted to use Pivot Table only. So I have tweaked the expression little and updating the new version.

                             

                            Also, just for your understanding - Synthetic Dimensions can be linked to the expression. But we can't link one Synthetic Dimension to another one. Hence I had changed my approach. And you can really optimize your expression if you have dynamic list. If its few values as part of Synthetic Dimension then you can hard code them otherwise you need to use ValueLoop() and Concat() to build the strings. I hope all this makes sense.

                             

                            Let me know if this is what you wanted to see...

                             

                            Thanks,

                            DV

                             

                            www.QlikShare.com