topic Re: Custom Totals in Table in QlikView

Custom Totals in Table

Sun, 07 Aug 2011 22:59:44 GMT

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

Custom Totals in Table

disqr_rm — Mon, 08 Aug 2011 02:00:17 GMT

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

Tue, 09 Aug 2011 16:09:16 GMT

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