Fact: Bears eat beets

It's April first. Your sales manager, Joe McImpatientpants, pulls up his dashboard and immediately calls you. Through the screams of how crappy your dashboard is, you divine that he's looking at the 'Current Quarter' tab. He's upset that everything is '0'! You take a deep breath to try to explain to him that his sales are '0' because it's the start of a new quarter and there haven't been any sales reported yet. You, of course, fail at this explanation because Joe McImpatientpants looks bad because his sales are '0'! Fix it, dammit! He forgot to slam his hand on his desk, so you don't take it seriously.

Since you're an analyst and you were hired because you always think you're right, you politely say that you'll fix it, hang up the phone, and call Joe McImpatientpants a few choice words under your breath.

Breathe. Smile. But not like a crazy person. More like Gandhi or a cat. But not like a monkey. Because showing your monkey teeth is a submission signal. When someone smiles at me with their monkey teeth, all I see is a chimpanzee begging for its life.

To fix it, we'll show the previous quarter's data for the first couple of weeks, then switch it to current quarter.

As a quick review, there are at least a couple of ways to set a dashboard to show current quarter data.

• Use a trigger to set the time frame dimension
• Use the time frame dimension in your set analysis

For this trick to work, you'll need to use the set analysis method. We'll use sales as an example:

     Sum({<[Timeframe] = {'Current Quarter'}>}[Sales])

(NOTICE: No '$' sign in this expression. See Bill Lay's explanation on Qlikboard.com.)

Step One

Create a new variable. Call it vDelayCurrentQuarter. In the definition, put:

     =If(Today() < QuarterStart(Today()) + 14,
       chr(39)&'Previous Quarter'&chr(39),
       chr(39)&'Current Quarter'&chr(39))

Today() returns the the current date. QuarterStart looks at the date inside the parentheses (in this expression, Today() ) and returns the first day of the quarter. chr(39) returns the single quote character. In plain English, this expression says:

"If the date today is less than 14 days into the quarter, give me 'Previous Quarter', otherwise give me 'Current Quarter'."

Step Two

Use the variable in your set expression:

     Sum({<[Timeframe] = {$(vDelayCurrentQuarter)}>}[Sales])

Don't forget to use the variable in your titles as well. You need to explicitly tell your users what they are looking at.

BAM! Now Joe McImpatientpants won't be calling you at the beginning of every quarter.

BONUS!

Some of your more sophisticated (lazy) users may need to adjust the delay. No problem. Just create another variable and an input box.

Call this variable vDelayPeriod. Check 'Predefined Values Only', 'Predefined Values in Drop-down', and 'Number Series'. Decide how many days your user should be able to delay.

Change your definition in the vDelayCurrentQuarter variable, replacing '14' with $(vDelayPeriod):


     =If(Today() < QuarterStart(Today()) + $(vDelayPeriod),
       chr(39)&'Previous Quarter'&chr(39),
       chr(39)&'Current Quarter'&chr(39))


Now, using the input box, your user will be able to decide how long after the beginning of the quarter that they should see 'Current Quarter'."

It's too bad that Time Cop was just AWESOME and not funny,  because a Time Cop joke would have fit much better with this tip. Be that as it may, if I could do the splits like the MUSCLES FROM BRUSSELS, I doubt I'd ever stand up again.

Bears. Beets. Battlestar Galactica.