List Your Values

We all have our own set of values. Some value love. Some value money. But just so we're clear, in the righteous words of David Lee Roth, money can't buy happiness but it can buy a huge yacht that sails right next to it.

Some value knowledge, but fear that we can't gain it. Fear of our own personal El Guapo. In a way, all of us has an El Guapo to face. For some, shyness might be their El Guapo. (Shyness is my El Guapo) For others, a lack of education might be their El Guapo. For our fearless leader, JD, being muy guapo is his El Guapo. For all of us, El Guapo is a big, dangerous man who wants to kill us. But as sure as my name is Lucky Day, the analysts of MarketStar can conquer their own personal El Guapo, who also happens to be the actual El Guapo! Which, translated from traditional Swedish means 'Qlikview'.

Why am I preaching values, you ask? Because I'm going to teach you how create your own list of values, or 'ValueList', if you will.

We often get requests to build complex tables in Qlikview that use various calculations that don't easily translate to simple dimensions and expressions. You might need to combine sums, counts, and averages over various time periods. Until now, we've create multiple one-row tables and had a heck of a time trying to fix the layout and come up with a totals row that made any sense.

As an example, one of our clients needed to look at deals for current and previous quarters, including Q^Q looks. Since there are 14 ways to look at the information, we built 14 individual, one-row tables. Needless to say, it was nearly impossible to lay it out. Even worse, it was absolutely impossible to export the information in any usable format. The fix is to use the ValueList() function.

ValueList() creates a synthetic dimension that you can reference in your expressions. The result is a single table that uses different calculations for each row. Notice in the table below that there are both counts and sums:

And for the most observant, you'll also notice that the number formatting is different depending on the row. So let's get to it.

Step 1

The first step is to create your synthetic dimension by using a calculated dimension. In the past, we've used calculated dimensions to manipulate existing data. An example is using 'If' statements to limit the dimension to certain values (e.g. If (Year = '2009', Year) to limit the chart to only '2009') A synthetic dimension creates values that don't exist in your data. The expression is simple. To get the values (rows) above, simply enter the following in the expression editor when you create your calculated dimension:


     ValueList('Bookings Above $50K',
               'Bookings Below $50K (Foundational)',
               'Bookings (non-ELA)',
               'Transacting Partners',
               'Number of Transactions*',
               'TPP*',
               'Average Deal Size*',
               'Ad+ Registered Value',
               'Ad+ Registered Count',
               'Ad+ Booked Value',
               'Ad+ Booked Count',
               'Ad+ Booked Avg Deal Size',
               'ELA Bookings',
               'Total Bookings (Includes ELAs)')


But, in order to simplify our expressions, we are going to create a variable and use $-sign expansion.

• Create your variable and enter the ValueList expression as the Definition (vMetricVL in this case)
• Use $(vMetricVL) as your calculated dimension

Now you have the beginnings of a 14-row table.

Step 2

Now for the tedious part. You need to tell Qlikview what calculation to make for each row. The basic theory is:

     "If the row value =  'xxx', then do 'yyy' "

In the table above,  you need to do this for each column. WARNING!! You are going to think to yourself, "Self, there's no way in H-E-Double-Toothpicks that I'm going to be able to write an expression that long!" Before you give up, just thing to yourself, "Self, this is way easier than keeping track of 14 different tables!" Plus, have I ever led you astray? No, not that you know of!


     If($(vMetricVL) ='Bookings Above $50K',
          Num(
               Sum({$< [TimeFrame] = {'Previous Quarter'},
               [ELA] = {'Non-ELA'},
               [Split] = {'Above $50k'}>} [Value])
          ,'$#,###.'),
     If($(vMetricVL) ='Bookings Below $50K (Foundational)',
          Num(
               Sum({$<[TimeFrame] = {'Previous Quarter'},
               [ELA] = {'Non-ELA'},
               [Split] = {'Below $50k'}>} [Value]),
          '$#,###.'),
     If($(vMetricVL) ='Bookings (non-ELA)',
          Num(
               Sum({$<[TimeFrame] = {'Previous Quarter'},
               [ELA] ={'Non-ELA'}>} [Value]),
          '$#,###.'),
     If($(vMetricVL) ='Transacting Partners',
          Num(
               Count({$<[TimeFrame] = {'Previous Quarter'},
               [ELA] ={'Non-ELA'}>} Distinct [PartnerID]),
          '#,###.'),
     If($(vMetricVL) ='Number of Transactions*',
          Num(
               Count({$<[TimeFrame] = {'Previous Quarter'},
               [ELA] ={'Non-ELA'},
               [Band2] -= {'$0 to $399'}>} Distinct [OrdID]),
          '#,###.'),
     If($(vMetricVL) ='TPP*',
          Num(
               (Count({$<[TimeFrame]= {'Previous Quarter'},
                    [ELA] ={'Non-ELA'},
                    [Band2] -= {'$0 to $399'}>} Distinct [OrdID])
          /
               Count({$<[TimeFrame] = {'Previous Quarter'},
                    [ELA] ={'Non-ELA'}>} Distinct PartnerID)),
          '#,###.#'),
     If($(vMetricVL) ='Average Deal Size*',
          Num(
               (Sum({$<[TimeFrame]= {'Previous Quarter'},
                    [ELA] ={'Non-ELA'}>} [Value]))
          /
               (Count({$<[TimeFrame]= {'Previous Quarter'},
                    [ELA] ={'Non-ELA'},
                    [Band2] -= {'$0 to $399'}>}Distinct [OrdID])),
          '$#,###.'),
     If($(vMetricVL) ='Ad+ Registered Value',
          Num(
               Sum({$<[TimeFrame] = {'Previous Quarter'}>} [Value]),
          '$#,###.'),
     If($(vMetricVL) ='Ad+ Registered Count',
          Num(
               Count({$<[TimeFrame] = {'Previous Quarter'}>} Distinct [Registrations]),
          '#,###.'),
     If($(vMetricVL) ='Ad+ Booked Value',
          Num(
          Sum({$<[TimeFrame] = {'Previous Quarter'}>} [Value]),
          '$#,###.'),
     If($(vMetricVL) ='Ad+ Booked Count',
          Num(
               Count({$<[TimeFrame] = {'Previous Quarter'}>} Distinct [Registrations]),
          '#,###.'),
     If($(vMetricVL) ='Ad+ Booked Avg Deal Size',
          Num(
               (Sum({$<[TimeFrame] = {'Previous Quarter'},
                    [Registrations] = {'*'}>} [Value])
          /
               Count({$<[TimeFrame] = {'Previous Quarter'},
                    [Bookings.DirectRepSegment] = ,
                    [Registrations] = {'*'}>} Distinct [Registrations])),
          '$#,###.'),
     If($(vMetricVL) ='ELA Bookings',
          Num(
               Sum({$<[TimeFrame] = {'Previous Quarter'},
               [ELA] ={'ELA'}>} [Value]),
          '$#,###.'),
     If($(vMetricVL) ='Total Bookings (Includes ELAs)',
          Num(
               Sum({$<[TimeFrame] = {'Previous Quarter'}>} [Value]),
          '$#,###.')
     ))))))))))))))

So, 14 'If' statements calling 14 different calculations, all depending on some values that you made up. Make sure to format your expressions with the Num() function.

Simple, right? Just do that two more times and you're done. If you're clever, and I know you are, you'll copy the expression and just change time frames.

Finally, for you Atomic Punks out there, a quiz. Which of the following were uttered buy the great David Lee Roth?

1. Rap is poetry to music, like beatniks without beards and bongos.
2. I used to have a drug problem, now I make enough money.
3. The only thing I'm allergic to is criticism.
4. I'm not conceited. Conceit is a fault and I have no faults.

If you're wrong, where have all the good times gone? You're no good! Jamie's crying! If you're right, you should dance the night away because you are running with the devil, little dreamer!