Each person in our company is a data source and it is important that they can easily input data that will enrich our analysis. If we combine analysis with planning, then we can be better prepared for the future and support our statistical analysis. In QlikView, business users can simultaneously analyze and input data.
Traditionally, data input in QlikView is limited to what-if scenarios that use variables or input fields. However, when we have a large number of variables, neither of these methods are ideal. A large number of variables is hard to maintain and input fields take up too much RAM. Furthermore, users cannot easily input a large number of values at once and there is a risk they will lose their input that is not directly saved to a database.
As an alternative, we have the option to use an extension that enables us to perform more advanced data input in QlikView. One of these extensions is called KliqPlan (http://www.ktlabs.com) and it allows users to input data directly into a relational database from a QlikView application. It can also read the content from a relational database in real time and allow users to reload a QlikView application from their browser.
In order to implement KliqPlan, you should be familiar with SQL and go through its manual to learn all the property options of its extensions. Although these property options are unique to KliqPlan, it reuses QlikView expression logic to dynamically calculate many property options.
If we have a tool like KliqPlan, sales representatives can simultaneously analyze their past sales data and input what they expect to sell over the course of the next month. We can facilitate their planning by using QlikView expressions in a KliqPlan table to propose a future sales amount. For example, we can use a rolling average to predict next month's sales but still allow the sales representatives to adjust it accordingly.
We can then use this information to better plan our purchasing and production activities. We can cross-analyze data from sales representatives with historical data to confirm that it is not exaggerated. We may even experiment with multiple forecast versions as we learn more about our sales process.
It is especially insightful to visualize actual and forecast data side-by-side like in the following figure:
We use rangesum() and set analysis to combine actual and forecast data into the same line. Our expression would be similar to the following code:
rangesum(
sum({$<_ActualFlag={1},_MonthSerial={'<=$(=max({$<_ActualFlag={1}>} _MonthSerial))'}>} [Net Sales])
,sum({$<_BudgetFlag={1},_MonthSerial={'>$(=max({$<_ActualFlag={1}>} _MonthSerial))'}>} [Net Sales])
)We then use the following code for the Line Style attribute expression:
if(_MonthSerial < max({$<_ActualFlag={1}>} Total _MonthSerial),'<s1>','<s2>')Along with the use of historical data, new products and customers are an important part of planning. Therefore, KliqPlan includes a component called KliqTable that allows users to add new customers and products.
KliqPlan is designed as a planning tool, but we can use it for any task that requires us to input data directly in QlikView. We often face analytical tasks that require the user to add information that cannot be found in any other data source. We usually end up using Excel files to input data that is not found anywhere else, but, in some cases, KliqPlan may be the better option, as in the following examples:
|
Options where KliqPlan is better |
Description |
|---|---|
|
Balanced Scorecard |
Often, the goals and actual results of some of the indicators do not come from any ERP, CRM, or other formal operational database. For example, we may not have software to manage training and every month somebody has to input the number of employees that are certified QlikView developers. Instead of using Excel a user can input this data directly into Qlikview with KliqPlan. |
|
Formal What-if Scenarios |
Before KliqPlan, we used QlikView's native input fields to develop financial what-if scenarios for a customer. They used a series of drivers, such as the daily number of customers and the average amount spent by each customer, to create an estimated profit and loss statement. We developed a macro to export the profit and loss statement in XML so that SAP BW could import it into its database. The user then had to wait for QlikView to reload the new information from SAP BW. This functionality can now be developed using KliqPlan. |