Maxed out

Today is Valentine's Day. Tomorrow you will be crushed by a 143,000 ton asteroid. It's worth almost $200 BILLION, so you should be able to rebuild your deck and get a new hot tub. Maybe even an name-brand Jacuzzi ! The only problem is that you only have a 1 in 3 Trillion chance of cashing in. Better if your house is bigger.

Movoto

While you're waiting, let's learn a new tidbit about the Max function.

Max() returns the numeric maximum value of expression or field iterated over the chart dimension(s). For us analysts, that means it gives us the biggest one. To illustrate, we'll look at a table that shows week over week.

A couple of things to notice:

• Sparklines give quick insight into the trend
• Some of the values are RED; RED means bad
• Each row is three lines high, adding readability and lightness

To build this table, use six expressions but hide two of them:

'Total Users' simply sums up the total number of active users.

'Weekly Users' counts the number of users that have logged in. Use Max in the normal fashion:

     = Count ({<[YearWeek] = {'$(=Max({1}[YearWeek]))'}>} Distinct [User])

A couple of things to note:

• Use $-sign expansion in conjunction with your set analysis to get the most recent YearWeek
• The identifier {1} ensures that you have the most recent YearWeek, regardless of your selections
• Don't forget to add any additional conditions into your set analysis

'Weekly Users -1' counts the number of users that logged in last week:

     = Count ({<[YearWeek] = {'$(=Max({1}[YearWeek],2))'}>} Distinct [User])

The only difference is the ',2' in your Max formula. It tells Qlikview to rank the values and that you want the second biggest. 

From there, it's just math using the column names:

This Week:

     = [Weekly Users] / [Total Users]

Last Week:

     = [Weekly Users -1] / [Total Users]

Trend. This is a little different, because you don't care about only having this or last week. You will need additional conditions in your set analysis, but it will start with something like:


     = COUNT (Distinct [User])
        /
        COUNT (Distinct Total  [User])


Don't forget to change the Representation to 'Mini Chart'.

And, voilà! You have a magical chart that will thrill and delight your users!

Of course, if you beat the 1 in 3 Trillion, you'll probably buy Maui, kick out everybody but your servants, and never open Qlikview again.

But at least you have this as a backup plan.