Sum of Sales Qty by last 3 Periods.

[Shahith]

Hi Everybody,

In the below picture, I have 2 columns, one is Fiscal Period as a Dimension and Sales Qty as a measure.

Here I Need to calculate the last 3 months' sum to display it in the current period, but with a catch!!

Here for example, for the period 2024004, the sum of sales qty should be '1644'(i.e. 1018+444+182, Dec, Jan & Feb's sum by not considering the March value).

Likewise, for 2024003 the sum of sales should be '3889'(2427+1018+444, Nov, Dec, Jan's sum by skipping Feb.)

Like this for all the periods. Any help is greatly appreciated! Thanks in advance!!

[Sayed_Mannan]

Hi, 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.