what is the magic formula?

My journey in the magical world of formula started in secondary school with algebra. Later in high school more formulae got introduced in trigonometry, statistics, physics and chemistry. In professional courses also complex formula was part of multiple subjects. Many of us kept a cheat sheet with common formula listed which was meant for revision just before an important examination. But with time I realized that memorizing formula was less critical as compared to understanding the context of the problem and then deriving the right formula to solve it.

Most modern analytics tool comes with a plethora of in-built functions which can be used to apply different formula across data columns. Besides, there are query languages which can be used to write a complex formula. This includes SQL, DAX, M, VizQL and others. But the challenge still remains in understanding & relating the business context, available data and objective so that the right metrics are derived and solve a business problem.

Once the data has been received from multiple data sources and cleansed after data preparation, data modeling is required to get the desired output. Simple summarizations such as sum, average, minimum, maximum and count are generally available for most fields. But even here using the right measure need to be contextual. For example, using count vs count (distinct) need to be carefully selected. Most other times, the data does not include information to answers the important business questions. In such cases measures which can work on relational data needs to be derived.

Spend buckets derived from amount or quantity columns is useful for Pareto analysis. Data rationalization from multiple unique values to a few useful actionable values can be derived using IF-THEN-ELSE function. WHAT-IF scenario can be constructed from derived metrics and is useful for decision making.

My favourite is one with time. Apart from simple time based trend, other metrics can be derived such as Year to Date, Previous Year to Date & Year on Year. This can be done for financial or calendar year. The same can also be done for quarter or month. Such derived metrics are very useful for variance identification and lead to timely corrective actions. Aging variables related to transaction status can be derived from date columns. Not only is this useful in accrual report, but also in asset utilization by finding out idle durations. Time between metrics can be derived and is useful in cycle time calculation for process optimization.

The best practice for data modeling is to understand the business process and its associated goals and KPI. Then work backwards and clinically access available data. Create derived metrics which would change based on interaction with data and provide flexible and quick results. Working with industry experts who understand enterprise processes would help decipher the magic behind that formula.

Let me know what you think.