Thursday, July 7, 2011

Creating Pivot Table in Qlikview to display Account Receivable Outstanding Amount from Lawson M3 data

In my last blog, I showed how to build a straight table using the Lawson M3 AR data. Continuing with the same document,  I will show how to create Pivot Table showing the outstanding amount in 0-30-60 days breakup. 

In the worksheet right click on the mouse button to bring the popup context menu, and click on New Sheet Object –> Chart to bring the Chart object designer as shown below.

Object 1

Type Pivot Table Outstanding Amount in the Window Title Text Box, and select the  Pivot table in the chart type (last icon on First row) .

Click Next button to bring the next window Dimensions. Here we can select the Dimension fields to be displayed in the pivot table. Highlight the fields CustomerNumber and CustomerName  in the Available Fields/Group box, and click Add. The highlighted fields will be moved into the Used Dimension box.

Object 2

 

Click the next button to bring the Expression Window and will display a popup window Edit Expression. Type Sum(If(DueDays <= 0 ,OutStandingAmount,0)) in the box and click OK button .

Object 3-1

 

Add additional 3 more Expression as shown below.

Object 3-2

Object 3-3

Object 3-4

 

Add the Labels to all the four expressions

Object 4

 

Click the next buttons and we will accept defaults in the Sort, Presentation and Visual Cues,Style windows.

On the number windows, select Money for Number Format Setting for all the expression as shown below.

 

Object 5

 

Click finish to display the Pivot table.

 

Object 3-6

2 comments:

Asaf Bar-ziv Meir said...

Great - post some more on MOVEX and QLIKVIEW - it will be nice.

venkat rao said...

Nice description on The biggest tables for Qlikview students and also should be shared the knowledge to everyone that your site provides. QlikView Training in Hyderabad