January 6, 2013

Paged charts in Qlikview

Qlikview does not have a built-in paging mechanism for charts and tables. By paging, I mean returning a portion of the results (say top 5 salespeople), with "next" and "previous" buttons to view other portions - in the same way a web search with Google or Bing returns one page at a time.

To illustrate, this is a simple paged chart in Qlikview:
 

Here's how to do it.
  • Create the following variables
    • vRows - contains the number of rows to display per page - 5 in this case
    • vPage - the page number. Initialise to 1 for the first page.
    • vRankFrom - the expression  =(vPage-1)*vRows+1
    • vRankTo - the expression  =vRankFrom+vRows-1
    • vPageCount - the expression  =Ceil(Count({1}DISTINCT Name)/vRows)
    • Note the = sign on the three expressions
    •  
    Set these variables
       
       
       
  • Create 3 text boxes
    • "Previous" or "<<"
      • Lable the first one "<<" or "Prev" (or whtaver makes sense in your situation). 
      • Create a set variable action for vPage with the following expression:
        =RangeMax(vPage-1, 1)
        .
        The RangeMax ensures that vPage will not be less than 1.
      • Optionally set the font colour to black, and add the following calculate colour expression:
        =If(vPage=1, White())
        (Set to whatever colours you like to indicate active and disabled states)
    • "Next" or ">>"
      • As above, but use =RangeMin(vPage+1,vPageCount) for the vPage set variable action and =If(vPage>=vPageCount, White()) for the calculated colour expression.
    • Page lable - this is a simple textbox displaying the expression:
      ='Page ' & vPage & ' of ' & Ceil(Count(Distinct Name) / vRows)
  • Create the chart. We need a chart with some sort of ordering such as ranking salespeople by sales volumes. In the example the chart expression is simply Sum(Value), and we will sort by this value. The dimension is the field [Name]. To adapt it to work with the ranking, use the following calculated dimension to limit the values to the current page:

    =Aggr(If(Rank(Aggr(Sum(Value), Name)) >= vRankFrom And Rank(Aggr(Sum(Value), Name)) <= vRankTo, Name), Name)

    (The ranking functions wrap the chart expression (red) returning the dimension values (blue) that fall inside the range specified vt the vRankFrom and vRankTo variables). 
  • On the sort tab, select Sort by "Y-value" descending.
  • One the Presentation tab,  check the "Max Visible Number" box and enter the expression =vRows in the expression box.
Here is a QV document that implements the paging described above:

DataPaging.qvw


Action for previous button
Colour settings for previous button

Max Visible Number setting on Presentation tab









5 comments:

  1. Hi,

    Thank you for the pagination technique. I am using your technique to put a table next to a bar chart, but I need to synchronize both of them.

    Is it possible for you to look into this issue and kindly let me know if there is any possible solution ?

    http://community.qlikview.com/message/372596#372596

    Thanks.

    ReplyDelete
  2. Hi Jo

    You have help me a lot in last few day at QV forum. now i am looking at your blog. 1 st post i read , you are very powerful in script.

    ReplyDelete
  3. Thanks for taking the time to discuss this, I feel strongly about it and love learning more on this topic. this is very nice one and gives in depth information. QlikView Training in Hyderabad

    ReplyDelete