Qlik Naming Standards
Table and Field Names
The quality and functionality of a QlikView application is to a large degree dependent on the names, descriptions and labels that are used to design construct and deploy a QlikView solution.
A full QlikView solution is a virtual structure build out of names, descriptions and labels. The QlikView solutions must be robust to carry this full data load unambiguously, from source to the front end. During this process it must translate pure data into meaningful information and insight for the end user.
During the transformation phase of the ETL, table and field names must be change to conform to the following convention.
Field Names
Field Names can be divided into the following four sections with slightly different rules for each:
- Primary Measures
- Time Dimensions
- Primary Dimensions
- Other Fields
Be careful not to use too long or to short descriptions. Use abbreviations and acronyms only if it is part of the daily business language of the client, like “SKU” or “UOM”.
Use the terms that is commonly known by the client and/or industry. Do not uses the term Product if, it is generally referring to as an Item. Older companies will refer to Sales Rep or Salesman. Don’t try to be politically correct and call it Sales Person. Just stick with the business terminology for that specific business or industry.
Primary Measures
A typical QlikView application will have between five and ten primary measures. It is important to spend extra time with the client’s business users to identify, clarify and define these primary measures.
Primary measures play a pivotal role in a QlikView application and its importance cannot be overstated.
In a QlikView Sales Analysis, the following will be typical primary measures:
- Quantity
- Sales
- Gross Profit
- Gross Profit %
It is important to take extra care, to use terms that are used by the industry and by the specific client. While the above terms might be good for a hardware company, it will not do for a resources company that would like to refer to the same concepts as Volume, Income, Gross Margin and Gross Margin %.
Measure Frames / Comparatives
In simple analysis the above measures might be sufficient, but it can get very quickly more complex if you start to introduce multiple Measure Frames (Comparatives).
Measure frames are the same measure in another context, like Budget, Forecast and Adjusted Forecast. This forces you to step back and appreciate the added complexity:
Actuals | Budget | Forecast | Adjusted Forecast |
Quantity | Quantity | Quantity | Quantity |
Sales | Sales | Sales | Sales |
Gross Profit | Gross Profit | Gross Profit | Gross Profit |
Gross Profit % | Gross Profit % | Gross Profit % | Gross Profit % |
When faced with multiple Measure Frames it is important to use the following type of naming convention:
Actuals | Budget | Forecast | Adjusted Forecast |
Actuals Quantity | Budget Quantity | Forecast Budget Quantity | Adj Forecast Quantity |
Actuals Sales | Budget Sales | Forecast Sales | Adj Forecast Sales |
Actuals Gross Profit | Budget Gross Profit | Forecast Gross Profit | Adj Forecast Gross Profit |
Actuals Gross Profit % | Budget Gross Profit % | Forecast Gross Profit % | Adj Forecast Gross Profit % |
Be careful with abbreviations. If the business users are comfortable to change Gross Profit to GP, go ahead.
Do not put a period at the end of an abbreviation. Adj Forecast Quantity presents in the front end application better than Adj.Forecast Quantity.
Time Dimensions
Time Dimensions are the Year, Month and Date fields that are used in a QlikView application. Keep it simple, logical and in line with the functional area (Sales, Operations or Finance) and business terminology. In most cases people will refer just to Year, Month and Date.
The name of date field that is linked to the calendar is important and is normally referred to as the Transactions Date. There could be other dates like Delivery Date, Shipping Date, etc. It is important to understand that Year and Month can only be linked to one Date (Transactions Date).
Be aware that in financial applications end users prefer to work in Fiscal Periods and that Fiscal Period 01 is not always January.
The following is a short list of standard time dimension descriptions:
- Year
- Month
- Transaction Date
- Day
- Week
Primary Dimensions
It is important to identify, understand and name the top ten to twenty primary dimensions. These fields are the key dimensions’ business users will use to look at their business, analyze its information, and use for slicing and dicing of the data.
Measures, Time Dimensions and Primary Dimensions will become the focal point of a QlikView application.
Keep primary dimensions short, simple and singular in the best possible business English. Call a spade, a spade and do the same with Customer, Vendor and Item.
Keep in mind that QlikView is different from ERP systems where everything is code driven. QlikView is there to serve the business users that knows things by their names and are not necessary familiar with the associated codes. We are not suggesting omitting code fields from the frontend, but they are playing a secondary and reference roll in the design of the frontend.
Codes and names can, in some cases be combined and presented as a single field. This rule works particularly well where you have sales people with common codes associated with them. By creating the following single field users can search either on the Sales Person Code or Name within the same field.
102 – Peter Smith
104 – Susan Kipling
210 – John Alan
The following is a list of typical primary dimensions. Added to it is the naming convention for the associated primary dimension codes.
Primary Dimension Codes | Primary Dimension Codes |
Customer Code | Customer |
Customer Group Code | Customer Group |
Customer Category Code | Customer Category |
Item Code | Item |
Item Category Code | Item Category |
Item Group Code | Item Group |
Sales Rep Code | Sales Rep |
Sales Rep Team Code | Sale Rep Team |
Region Code | Region |
Area Code | Area |
Vendor Code | Vendor |
Vendor Group Code | Vendor Group |
Vendor Category Code | Vendor Category |
Brand Name Code | Brand Name |
Company Code | Company |
Shop Code | Shop |
“DESCRIPTION”
Refrain from using Description as part of a field name. It takes up too much space, stating the obvious and is technically not correct. A lot of primary dimensions are going to end up as column headers and/or in the caption of list boxes. You want the list box or column to read what it is: Customer, Vendor or Item.
Field Names that flow into the final application should be ready to use as they are, and without the need to redefine or restate their meaning.
Description can be used, almost as an exception to describe something like Item Description, where it is an actual and real short or longer description of the Item.
MULTIPLE SIMILAR DIMENSIONS
Keep primary dimensions logically together in the alphabetic listing by the way you name it. In cases where you are faced with a Sold to Customer and Ship to Customer use the following syntax:
Customer (Ship To) Code | Customer (Ship To) |
Customer (Sold To) Code | Customer (Sold To) |
If the general business understanding is that Customer always refers to the Sold to Customer you can use the following syntax:
Customer Code | Customer |
Customer (Sold To) Code | Customer (Sold To) |
Other Fields
Other Fields represent all other fields not mentioned above that complete the QlikView application. Most of them would be additional information like Addresses, Telephone Numbers, and Invoice Numbers etc.
Do not to bring fields into the final QlikView application for which you do not have a specific need.
If you have multiple sets of fields like Addresses or Telephone Numbers, use a logical prefix to group them together.
Customer | Supplier |
Customer Code | Supplier Code |
Customer Address 1 | Supplier Address 1 |
Customer Address 2 | Supplier Address 2 |
Customer City | Supplier City |
Customer Zip Code | Supplier Zip Code |
Customer Telephone Number | Supplier Telephone Number |
