To calculate the last 3 months' sum to display it in the current period while skipping the most recent month in Qlik Sense,you can use a combination of Set Analysis and RangeSum functions. Here’s how you can achieve this:
Step-by-Step Solution
1. Load Data: Ensure your data is loaded correctly with Fiscal Period and Sales Qty fields.
2. Create the Measure: Use a measure with Set Analysis to calculate the sum of sales for the required periods.
Measure Expression
We will create a measure that sums the sales quantities for the last 3 months while skipping the current month.
Measure for Current Period Sum Excluding the Most Recent Month
Use the following expression for the measure:
RangeSum(
Above(
Sum({<FiscalPeriod = {"$(=Date(AddMonths(Max(Date#(FiscalPeriod, 'YYYYMM')), -1), 'YYYYMM'))"}>} SalesQty) +
Sum({<FiscalPeriod = {"$(=Date(AddMonths(Max(Date#(FiscalPeriod, 'YYYYMM')), -2), 'YYYYMM'))"}>} SalesQty) +
Sum({<FiscalPeriod = {"$(=Date(AddMonths(Max(Date#(FiscalPeriod, 'YYYYMM')), -3), 'YYYYMM'))"}>} SalesQty)
, 1, 3)
)
Steps to Implement in Qlik Sense:
1. Load Your Data: Ensure your data includes the `FiscalPeriod` and `SalesQty` fields.
2. Add a Table: Add a table visualization with `FiscalPeriod` as the dimension.
3. Create Measure:
- Click to add a measure.
- Paste the above expression into the expression editor.
By following these steps and using the provided measure expression, you should be able to display the sum of sales quantities for the last 3 months, excluding the most recent month.
