Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Take a look here. You may need to take this (P&L) approach to produce such tables.
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