Help me Rhonda

There were a lot of good things about the '80s. Apple Macintosh. Yugo. Fall of the Berlin Wall. Cyndi Lauper. Die Hard. My Gold Medal at the 1980 Summer Olympics. Ronald Reagan. Brits kickin' it in the Falklands Islands. DeLorean DMC-12. MTV. Pac-Man.

Unfortunately, the Beach Boys concert at Park West in 1982 was not one of them.

Help me Rhonda! I spent 12 bucks to see a virtual Bear Country Jamboree? There's no way those dudes were still alive! Animatronics DO NOT ROCK! Van Halen ROCKS! But don't get me started on Van Hagar...

Which brings me to the simple fact that I learn new QlikView things because people come to me for help. The latest was a request to only show the top 50 partners (in sales) no matter what filters were selected. On its face it's a reasonable and simple request. As a sales manager, I really don't care about the ankle-biters. I only want to see the Partners that make a difference.

Then I started to ponder the details.


Lesson #1 Don't Assume It's Simple

• Ask a lot of questions. Think about how the request might be used and ask about it. Questions that start with 'What happens when...' are a really good place to start.
• Listen to what the answers mean, then clarify. Questions that start with 'You mentioned....What does that mean?' are a good followup.
• After your probing questions, ask them to restate the request. Something like 'OK, now that we've talked through it, can you ask it again?' I know it's annoying, but the guy asking for help know what he needs.

Lesson #2 Use the QlikCommunity Forums

For this request, I spent some time thinking about possible solutions and was able to come up with something that was wrong, but I was sure I was on the right track. Using that pre-work, I was able to come up with a question that made enough sense that someone was able to help. In this case, Ajay Prabhakaran (forum thread) was able to take my humble start and figure out the correct solution.

On to the solution...

Step One - Create a Trigger

Create a 'Select in Field' Trigger (Document or Sheet, depending your requirements).

In this example, we'll select the top three cities by sales.

• Enter City as the Field
• Enter ='(' & Concat(If(Aggr(Rank(Sum(SalesAmount)),City)<4, City),'|') & ')'

I'll grant you that it's an ugly expression. But you know what else was ugly? Cabbage Patch Kids. Also '80s.

What does it do?

1. Ranks Cities by Sales
2. Concatenates those cities with a | (pipe)
3. Adds parentheses around the result

Step Two - Create another Trigger

This one is much simpler. Create a trigger (Document or Sheet to match the first trigger) to lock the City field so that when your users clear filters, it doesn't clear the top 3.

Step Three - Use It In Your Fight For The Right To Party

Word.