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

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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