QlikView Your Business

Chapter 13 Data Modeling for Inventory Analysis

In this chapter, we discuss advanced modeling techniques that will be used to build the data model for the Inventory Analysis application.

You will learn the data modeling concepts that lie behind the following techniques:

• Calculating running balances
• Building data models with Concatenated Fact tables
• Advanced date-handling techniques

Calculating Running Balances

In this section, we introduce the logic of calculating running balances and how it applies to inventory and inventory aging.

The Logic of Calculating a Running Balance

Imagine a traditional bank statement. It starts with a beginning balance, followed by a list of transactions—deposits and withdrawals. An intermediate balance is presented next to each transaction. We usually call it a running balance. Running balances are not always stored with the transactions. Instead, they are calculated on the fly. The logic of calculating a running balance is relatively simple. Each running balance is equal to the previous value of the same balance, plus the value of the current transaction. The running balance in the next line will use the current balance as its base and so on. This logic is very easily implemented in an Excel formula.

In QlikView, however, calculating running balances on the fly could be too costly from the performance standpoint. It is much better if we could pre-calculate running balances and store them in the data.

The overall logic is still the same. However, ...