Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mburckhardt
New Contributor

Pivot table MTD calculation for current month

Hi all,

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.

Regards

Community Browser