Link

Calendar View

Last week a customer asked me for a simple –but quite friendly– visualization for the daily sales. These guys are in the fast food business, and their operational supervisors (one on each restaurant across the country) needed a general overview of the daily behavior of the sales, orders and average ticket. My first draft was a straight table with the main KPIs compared to the last year accompanied by this chart:13.14

The blue line represents the current year while the gray one embodies the last one. As you can see, their sales are extremely predictable: slow weekdays and busy weekends. Sadly, they didn’t feel comfortable with it. They could compare a Thursday with its contiguous days (Wednesday and Friday), but it was hard to compare it with other Thursdays.

The answer was clear; they needed a Cross Table that involved the weekday. However, when I created the object, I realized it was hard to read. In the end, I came up with this:

13.01

Quite simple, but it covered the operational needs. Here’s how to do it. As always, you can download the QVW file here.

Tutorial

1.- First of all, let’s create some fields in the script:

13.02

2.- Before starting, be sure to select only one Month-Year (Our visualization won’t work otherwise). Create a Straight Table and use Week as dimension.

3.- In this object, we are going to create two expressions for every day of the week: “Day” and “Sales”. We will start with “Day” column for Mondays:

13.03

Use ‘M’ as label and select No Totals in the Total Mode.

13.04

Open the menu in this expression to change its Background Color and Text Format:

13.05

13.06

4.- Create a new expression for the sales amount:

13.07

Use a space as label (just to hide the expression definition). Don’t forget to choose the Money format in the Numbers tab.

Your table should look like this:

13.08

5.- Copy and paste these columns 6 times and change the filter in the Set Analysis clause to visualize the corresponding day.

6.- Now let’s create the “Week Total” column by creating this expression:

13.09

And its corresponding sales amount:

13.10

After these steps, the structure should be ready. However, we still need to make some visual adjustments.

13.11

7.- In the Presentation tab:

  • Center the labels for every column
  • Select the Week dimension and hide it
  • Totals on last row
  • Wrap header and cell text (2 lines)

13.12

If you want to get rid of the zeros, you can use an IF statement like this:

13.13

If anyone has a better way to do this, please share! I feel like it’s a little dirty to use these kind of expressions 😛

8.- Voilà, our calendar view is ready.

13.01

You can complement it with a Calculation Condition, a friendly error message or the Always One Selected Value option in the MonthName field.

If you have any comments or suggestions remember to post them in the section below!

Advertisements

10 thoughts on “Calendar View

  1. Hi Julian,

    Did you think about adding another Column for each Day to show an image if that Week is less than or greater than the previous Week

    ▲ or ▼

    That would be really cool

    Regards

    Alan

  2. I have added it for other viewer of this bloq

    Create another Column next to each Day, with the Following Expression

    IF(
    sum({$} Sales) >
    above(sum({$} Sales))
    ,’▲’,’▼’)

    and for the Expression Definition colour font use

    IF(
    sum({$} Sales) >
    above(sum({$} Sales)),
    RGB(0,255,0),
    RGB(255,0,0))

    • I just tried it and it looks great, especially with the colors. Great addition!

      It also works pretty well with a heatmap, have you tried it?

  3. Andrey says:

    Hi Julian,

    you can get rid of the zeros in a more elegant way: just modify the number format for the numeric columns and set it to this (you then don’t need the if() statement around the expression):

    $#,##0;-$#,##0;

    The trick is the last part of it (empty string), which essentially tells QlikView to format zero values with “nothing”.

    Regards
    Andrey

  4. Karol Fabisz says:

    Andrey, what do you mean by saying “Before starting, be sure to select only one Month-Year (Our visualization won’t work otherwise).”.
    Regards
    Karol

    • Since the visualization is based on the Week field, it will technically work even if you select multiple months of the same year. However, the comment had to do more with usability than technical issues. Several months won’t look so good in the same table (a lot of information in a single table) and it would be hard to identify where each period starts and ends. Besides, we’re used to see one month per page in our physical calendars 😛

      Also, if you select two equal months (for example, two Octobers), the formulas will do some strange things.

      I hope it helps!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s