Thursday, August 21, 2008

Changing Daily Data to Weekly in a Chart


I got a call earlier today from an analyst who had a QlikView report loaded with detailed shipment data. The main chart on the report showed shipment totals by day but the analyst needed the chart to show weekly quantities -- "is there a way to make it show the weekly data?"

"Well, you're in luck. Not only can it be done but you can do it yourself in a few minutes." Follow these steps:
  • Open the QlikView report and save a copy of your report under a different name by selecting File->Save As from the menu (just in case I give you bogus instructions that don't work... it happens).

  • Right-click on the chart that shows the daily data. Select Properties and select the Dimensions tab. It might look like the diagram above. [Click on that diagram to make it bigger and get a better view]

  • You'll see a SHIPDATE entry in the Used Dimensions window. Click on it and then click the Remove button

  • Now, click on Add Calculated Dimension... the Edit Expression window opens up. We're going to add an expression that yields a weekly date to replace SHIPDATE. Type in this expression:
    WeekStart(SHIPDATE)

  • Click OK to close the Edit Expression window

  • Give our new dimension a name- type WEEK in the field name box. See how it might look in the diagram below.

  • Click OK to close the Chart Properties window


Now the chart shows data by WEEK instead of SHIPDATE. This same method can be used for other types of date conversion functions or most other kinds of numeric or text string calculations. It lets you use a chart dimension even though the dimension data doesn't actually exist that way in the loaded data.

1 comment:

FinMagic said...

WeekStart is a function that returns the previous Monday for the date supplied.