Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Ashishterra
Contributor
Contributor

how to get Dynamical sum of last 3 months data from VERSION_2 column in pivot table

How to get Dynamical sum of last 3 months data from VERSION_2 column in pivot table, so from VERSION_2 column it should pick latest 3 months to sum the FY_TOTAL column.

In below image u can see i have 3 columns i have calculated Mar'25 measure now wanted to calculate Actuals.

Actuals column= sum of all months prior to the current month of the current FY ​

Example: if the current budget is “Mar'25”, actuals = Feb'25 + Jan'25 + Dec'24.

but the condition is if user choose Mar'25 through filter pan it should dynamically pick the previous 3 months exlcuding choosen one and get the sum.

How to get this?, I tried below Expression not working as expected.

If(
vIncludeCF = 'IncludeCF',

// --- Include CF Logic ---
(
Sum({
<VERSION=,CURRENT_VERSION=,
[VERSION_2.autoCalendar.MonthYear] =
{"=Match([VERSION_2.autoCalendar.MonthYear],
$(=Concat(DISTINCT TOP 3
TOTAL Aggr(Max([VERSION_2.autoCalendar.MonthYear]), [VERSION_2.autoCalendar.MonthYear]), ','))
)"}>
} FY_TOTAL)

+

Sum({
<VERSION=,CURRENT_VERSION=,
[VERSION_2.autoCalendar.MonthYear] =
{"=Match([VERSION_2.autoCalendar.MonthYear],
$(=Concat(DISTINCT TOP 3
TOTAL Aggr(Max([VERSION_2.autoCalendar.MonthYear]), [VERSION_2.autoCalendar.MonthYear]), ','))
)"},
CUSTOMER = {'PP'}>
} CF.FY_TOTAL)
) / 1000000,

// --- Exclude CF Logic ---
Sum({
<VERSION=,CURRENT_VERSION=,
[VERSION_2.autoCalendar.MonthYear] =
{"=Match([VERSION_2.autoCalendar.MonthYear],
$(=Concat(DISTINCT TOP 3
TOTAL Aggr(Max([VERSION_2.autoCalendar.MonthYear]), [VERSION_2.autoCalendar.MonthYear]), ','))
)"}>
} FY_TOTAL) / 1000000
)

Ashishterra_1-1747122466456.png

 

 

0 Replies