Avoiding errors in Pivot based formula executions

There might be times when you have two pivots and each might have common, uncommon or maybe unequal number of keys in them. These characteristics might affect the result when you are applying some data function in a formula and passing the pivots as variables.

Consider a situation wherein you have two pivots, one each for current data and previous data, and each pivot has got company wise sales in them.

Now, let’s say in the previous year there were four companies present in the data and hence the previous year’s pivot has got four keys, say ‘C1’, ‘C2’, ‘C3’, ‘C4’.

In the current year however, an additional company ‘C5’ has come up in the data and hence the current year’s pivot has got five keys ‘C1’, ‘C2’, ‘C3’, ‘C4’, ‘C5’.

Further, let’s say you want to calculate the company wise change in sales using the ChangePercentage data function.

ChangePercentage(prev_sales_pivot, curr_sales_pivot)

This formula shall result in an error since the number of keys in both the pivots is unequal.

To avoid this, always keep the keys which are common to both the pivots.

To implement this, you can use the CommonUnique data function. Using this function you can get the companies that are common to both the years and then filter the pivots while creating them to hold only the data for those particular keys

curr_companies = Unique('Company') || Filter on current data
prev_companies = Unique('Company') || Filter on previous data

common_keys = CommonUnique( curr_companies, prev_companies )

curr_sales_pivot = GroupbyCategory('Company', 'Sales') || Filter on current data and common_keys

prev_sales_pivot = GroupbyCategory('Company', 'Sales') || Filter on previous data and common_keys

Now when you pass these pivots in the ChangePercentage function, there won’t be any error.

Please Note: The above method opted is one of the methods to ensure that the keys in the pivots are the same. However, there can be other ways to carry out the same. The idea presented above is to convey the importance of having the same number and also the same keys in the pivots in case you are trying to operate using pivot based calculations.