2 Replies Latest reply: Aug 9, 2011 12:10 PM by Simon Haugbølle RSS

    Custom Totals in Table

    Simon Haugbølle

      Hi (again)

       

      I'm trying to reproduce a table that I created in Excel a while back. In QlikView, doing simple totals is swift, but what if I want to do advanced totals (and multiple rows of them)?

       

      I would really love some pointers, so I don't waste time going in the wrong direction. I have tried searching the community, but I couldn't find someone with the exact same problem. I found discussions on formatting of the standard totals, but nothing on altering the expression used in totals (and still I would need not just one total, but 3-4 totals [subtotals]). There was something about the dimensionality() function, but I don't see how this would help me?

       

      I think the solution is to create a table with the actual output (perhaps using SQL). Is there any way to add rows to a table in QlikView? Could I actually create a table from another table in QlikView?

       

      Any ideas, links etc is greatly appreciated...

       

      Thanks

       

      // Simon

       

      CustomTotals01.jpg

        • Custom Totals in Table
          Rakesh Mehta

          Take a look here. You may need to take this (P&L) approach to produce such tables.

           

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

            • Re: Custom Totals in Table
              Simon Haugbølle

              Hi Rakesh

               

              Thanks for your reply!

               

              I have looked at your suggestion, but I must say I find it a bit hard to follow. I think I have found another solution though. I haven't had time to implement it yet, but I think it will work. It is inspired by this blog post: A Chart Expression That Works Differently for Totals Row.

               

              What I want is to always show the top 10 areas, so I will use a straight table with a Max Number of 13 in Presentation tab in Chart Properties. One might ask why 13? Well, I need the last three rows to be replaced by my own totals, so I will use a formula like the one below

               

              If(RowNo()=11,

              //do something,

              If(RowNo()=12,

              //do something else

              If(RowNo()=13,

              //do something else

              , Sum(Sales YTD))))

               

              The only things I worry about are: How to sort the table, but I think that can be accomplished by maybe inserting a column that is hidden, which runs Sum(Sales YTD). Also, it is not certain that there will be 10 areas, so I might need to implement some kind of NoOfRows-function... Finally, I will also need to turn off regular totals...

               

              I will report back, when I have worked it out.

               

              // Simon