I am comparing sales data to budget data in a pivot table. The rows are countries, the columns are a drill down: month -> category -> product. The calculation of the deviation is fairly easy: to get the total difference the formula is sum(sales) - sum(budget). But for the current month I don't want to compare to the budget of the whole month but only part of it: current num of working day / num of working days in the month.
The way I implemented it (in pseudo code) at the moment is as follows:
if no month selected (because of drilldown)
// we see country rows, month columns
then if ColumnNo = num(CurrentMonth)
// current month column
then sum(sales) - sum(budget) * num working day / total working days in month
// completed months
else sum(sales) - sum(budget)
// we see country rows, category/product columns
else sum(sales) - sum({month < current month} budget) - sum({month=current month} budget) * num working day / total working days in month
At the moment it works, but it is pretty slow, additionally the users want an alternative dimension to switch to a different drill down: categories -> month. My implementation won't work then. Does anyone know how to implement this differently? I would like to get rid of all the ifs, since they seem to be causing the performance issues.
