The transformation from "ha ha dad that's funny" to "horrified" to "pleading 'no don't make me do this!'" to "you're not my dad, what did you do with my dad!?!?" to "resignation" to "this is funny!" is almost too much to bear.
And now I bear the responsibility of sharing some QlikView awesomeness with all of you...
Chapter One - Background
Sales teams generally have a hierarchical structure. General management will want to see everything. A district manager only wants to see detail on his district, but might want to know how the company is doing. Same with a rep. In short, regardless of my level, I want to see the detail on what I'm responsible for and I also want to know what's happening upstream. On the flip side, I might not want my reports knowing the details of their peer's business.
The trick is to filter the data
For this example, we'll use the following hierarchy:
Business - The whole enchilada (MarketStar)The user of the dashboard is the sales rep and will use the information in their day-to-day sales motions.
Region - Part of the Business, made up of multiple Territories (Great Lakes)
Territory - A single sales rep's list of partners (Michigan)
What we'll end up with is a table that uses dimension labels based on selections:
In one little table, the user can see where their partner's performance stacks against their territory, team and company.
There are a lot of steps to this technique. Let me 'splain. No, there is too much. Let me sum up.
- 'Hide Prefix' fields
- Variables... a plethora of variables
- Triggers
- Value Lists
Chapter Two - Pseudo Section Access
Normally, a developer will use Section Access to reduce the data set so that the end user will only see their information. The problem with reducing the data is that it's not there anymore to use in any calculations. If we reduce the data to only include Michigan, how do I get Great Lakes attainment? You don't.
Step One, Chapter Two - Hide Prefix
Hide Prefix is a QlikView function that allows a data field to appear as a system field. For our purposes, that means any selections of these fields won't show up in the Current Selections object. Since this is a design blog, we won't go into depth on the creation of these fields. But for those curious folks:
- Hide Prefix is designated by a character of your choice. I use an underscore ( _ ). You will need to declare it in the script with SET HidePrefix = '_';
- Create your fields. There are various ways to do it, but something like LOAD Territory AS _Territory will be needed. Do the same for District.
Step Two, Chapter Two - SetAccess Variables
Here's the tricky part. We will create a couple of variables that will look at the login of the person using the dashboard and then set our two new Hide Prefix fields. The syntax of the variable is fairly simple, but if you have a lot of users, upkeep may be complicated.
I've named the first variable vSetTerritory:
If(OSUser() = 'NT\rbruso', 'Michigan', //Rich BrusoNotice that I've put the name of each user as a comment at the end of each line. Doing so will help your maintenance and upkeep. Create another for your District
If(OSUser() = 'NT\blatimer', 'Wisconsin', //Bryce Latimer
If(OSUser() = 'NT\mmarcum', 'Indiana-Kentucky', //Mark Marcum
If(OSUser() = 'NT\mchang', 'Illinois' //Matthew Chang
))))
If you're unsure of the format of the output of OSUser(), put it into a text object.
Step Three, Chapter Two - Triggers
Prince Humperdinck never tells us the name of his horse, but I have it on good authority that the Lone Ranger stole it for his horse. And since boys who don't cry wanna be cowboys and need t-t-t-t-triggers (look it up, yippee-yi-yo), let's create a couple.
Open the Triggers tab in the Document Properties (Settings > Document Properties).
Click on 'OnOpen' in the Document Event Triggers box, then click Add Actions. You'll see the normal Triggers/Actions dialog.
Unless you have a reason not to, add a Clear All action. Then two Select in Field and two Lock Field actions.
Use your new Hide Prefix fields and the search string for your Select in Field will use $-sign expansion with your variables that you created above.
=$(vSetTerritory)
Then create actions to lock those fields.
CAUTION: If you've taught your users to use Lock/Unlock, they may figure out how to clear these selections. There are various ways to make that impossible (extensions, macros), but we'll save that for another joke post.
The result is that you've 'selected' values in fields that your users can't see. You've 'reduced' their data to their area of responsibility. But, as a clever designer, you can still aggregate data at a higher level.
Chapter Three - Value Lists
We've gone over Value Lists before. They are simply synthetic dimensions combined with some nasty-looking-but-relatively-simple expressions. This technique will dynamically create synthetic dimensions based on selections.
Step One, Chapter Three - More Variables
Normally, we'd create a variable to use in your Value Lists that would have static labels. Something like:
Start with defining each of the values for Partner, Territory and Region
Notice that if the user has nothing (or multiple) selections, the Value List tells them to make a selection. Once the selection is made, then the Value List label will reflect that choice.
Step Two, Chapter Three - More Variables, Again
Now for the magic.
Create a new variable to use as your Value List. Call it vRepVL:
Chapter Four - The Object
Even though I'm all for fancy for fanciness' sake, there is a point to all of this. We're making a straight table that has the context to be used anywhere. Export it, copy it, look at. You just get it because it says what it means.
Step One, Chapter Four - The Dimension
This is easy; we've done it before. Create a calculated dimension with:
Step Two, Chapter Four - The Expression
There are two twists to your expression:
Make sure that your columns give relevant information. Sparklines, Year over Year, Attainment... What ever makes sense to your audience.
Step Three, Chapter Four - Formatting
You know what I like. Give it some white space. Get rid of your crappy colors. Lighten it up.
Epilog
This is where you sit back, brag to your peers and grumble about your users' lack of appreciation for your wonderfulness.
Then continue your search for the six-fingered man. You know what to do when you find him...
Chapter Three - Value Lists
We've gone over Value Lists before. They are simply synthetic dimensions combined with some nasty-looking-but-relatively-simple expressions. This technique will dynamically create synthetic dimensions based on selections.
Step One, Chapter Three - More Variables
Normally, we'd create a variable to use in your Value Lists that would have static labels. Something like:
ValueList('Partner','Territory','Region','Company')That's fine, but not terribly descriptive. Wouldn't it be better if 'Partner' was actually the name of the Partner the user selected in the List Box? And 'Territory' was the name of the Territory we set for them with our Triggers? ('Yes' is the correct answer here) To make it happen, let's nest some variables.
Start with defining each of the values for Partner, Territory and Region
- vPartner
- If(GetSelectedCount([PARTNER]) <> 1, 'Pick a Partner', GetFieldSelections([PARTNER]))
- vTerritory
- If(GetSelectedCount([_Territory]) <> 1, 'Pick a Territory', GetFieldSelections([_Territory]))
- vRegion
- If(GetSelectedCount([_Region]) <> 1, 'Pick a Region', GetFieldSelections([_Region]))
Notice that if the user has nothing (or multiple) selections, the Value List tells them to make a selection. Once the selection is made, then the Value List label will reflect that choice.
Step Two, Chapter Three - More Variables, Again
Now for the magic.
Create a new variable to use as your Value List. Call it vRepVL:
ValueList(Let's look at what it's doing, using '$(=$(vPartner))' as the example:
'$(=$(vPartner))'
,'$(=$(vTerritory))'
,'$(=$(vRegion))'
,'MarketStar')
- $(vPartner) extracts the text from vPartner
- =$(vPartner) runs the expression contained in vPartner
- '$(=$(vPartner))' brings back the result of =$(vPartner) as text
So, as an example... If the user selects "Tech Enterprises" in the Partner List box, QlikView will see the first part of the Value List as:
ValueList(
'Tech Enterprises', ...
Chapter Four - The Object
Even though I'm all for fancy for fanciness' sake, there is a point to all of this. We're making a straight table that has the context to be used anywhere. Export it, copy it, look at. You just get it because it says what it means.
Step One, Chapter Four - The Dimension
This is easy; we've done it before. Create a calculated dimension with:
=$(vRepVL)Make sure to give it a good Label.
Step Two, Chapter Four - The Expression
There are two twists to your expression:
- Replace the matching value of your Value List with the corresponding variable
- Add an extra 'If" so that if no (or multiple) value is selected, nothing shows up on that row
Your new expression, making sure that you add any applicable set analysis, will look something like:
If($(vRepVL) = $(vPartner),
If(GetSelectedCount([PARTNERS]) <> 1, '',
Sum([Sales])),
If($(vRepVL) = $(vTerritory),
If(GetSelectedCount([_Territory]) <> 1, '',
Sum({<[PARTNERS]=>} [Sales])),
If($(vRepVL) = $(vRegion),
If(GetSelectedCount(_Region) <> 1, '',
Sum({<[PARTNERS] = , [_Territory] = >} [Sales]),
If($(vRepVL) = 'MarketStar',
Sum({<[PARTNERS] = , [_Territory] = , [_Region] = {"*"}>} [Sales])
Step Two, Chapter Four - The Dimension, Again
Make sure that your columns give relevant information. Sparklines, Year over Year, Attainment... What ever makes sense to your audience.
Step Three, Chapter Four - Formatting
You know what I like. Give it some white space. Get rid of your crappy colors. Lighten it up.
Epilog
This is where you sit back, brag to your peers and grumble about your users' lack of appreciation for your wonderfulness.
Then continue your search for the six-fingered man. You know what to do when you find him...
No comments:
Post a Comment