Pivot Table Month to Date Sum

[clifford]

I'm trying to add a column to this pivot table that is month to date sums of plan and real for each day. I want it to reset each month. Thank you.

[diegozecchini]

Hi
To create a "Month-to-Date" (MTD) sum in a Qlik Pivot Table for fields such as Plan and Real, follow these steps:

Ensure your pivot table has dimensions like Date and Month.
If Month is not directly available, you can extract it from the date field:

Month(Date) AS Month
Then use the RangeSum and Above functions to calculate the MTD sum dynamically in the pivot table.

RangeSum(Above(Sum(Plan), 0, RowNo()))
RangeSum(Above(Sum(Real), 0, RowNo()))

These expressions calculate the cumulative sum of Plan and Real for all rows above the current row within the same column.

To ensure the MTD calculation resets at the beginning of each month, use a combination of the Aggr function and the Month dimension:
RangeSum(Above(TOTAL Sum(Plan), 0, RowNo(TOTAL)))

Add Month and any other grouping dimensions (e.g., Year) to the Aggr context to isolate calculations for each month.

Add Columns to Pivot Table: Add the above expressions as measures in your pivot table:
Label one column "MTD Plan".
Label another column "MTD Real".

Hope it helps,

Diego