In this Tips and Techniques article, the LOOKUP function is our main focus.
To add the example Klip used in this article to your Dashboard click this link. Example Klip - Lookup
The LOOKUP function is selected typically when the data in your data source is inconsistent. Simply said, it keeps your data aligned, even though there might be gaps within the data set.
The LOOKUP function consists of three parts:
- The first part is "input". For example, thsi mgiht be a list of all possible dates. You can use the REF button here to refer to the X Axis,
- The second part is "keys". For example, this might be a list of all available dates.
- The third part is "result". This is what you want returned.
Tip: The second and third part of the LOOKUP formula must evaluate to the same number of items.
In our Support desk example,
The X: Axis displays the dates that are equal to or greater than a selected report date. The report date is a variable.
The Series returns the total number of iphones that match the selected "reportdate" range.
The AND function means the result must match iphones and the selected report date.
We will look at variables later, but for now, create a new variable called "reportdate" in a Drop-Down list, User Input Control component.
The "Values" formula is simply:
GROUP (A:A) which returns a unique date in a drop-down list.
The formula for the X:Axis (Date) is:
GROUP( SELECT( A:A , ( DATE ( A:A , “M/d/yy“ ) >= DATE( reportdate , “M/d/yy” ) ) ) )
Results in a unique date instance greater than or equal to the date selected.
The formula for the Series is:
LOOKUP( Date , ( SELECT( A:A , (B:B = “iphone” ) ), ( SELECT( C:C , (B:B = “iphone” ) ) )
Now that you know more about the LOOKUP function, check out the following tutorials:
- Tips and Techniques - Function examples
- Tips and Techniques - Task examples
- Tips and Techniques - Google Analytics examples
These articles provide an overview of important concepts for building Klips: