Each Customer Join Plan Month retrieve next three month balance. Month+3

[eddywong71]

Hi All,

I want to retrieve the data as table like below.

Customer Join Plan Month                    Balance

202301                                                           202304

202302                                                           202305

202303                                                           202306

For Dimension :

Customer Join Plan Month     

For expression:

SUM({$<Month ={' AddMonths($(vMaxMonthYear)-3)'}>}[Balance])

However this expression only can retrieve the latest month balance (202309)of each Customer Join Plan Month like below table

Customer Join Plan Month                    Balance

202301                                                           202306

202302                                                           202306

202303                                                           202306

 After I Refer this link : The As-Of Table - Qlik Community - 1466130.

tmpAsOfCalendar:
Load distinct Month
Resident [master_bb_base_all] ;

// ======== Cartesian product with itself ========
Join (tmpAsOfCalendar)
Load Month as AsOfMonth
Resident tmpAsOfCalendar ;

// ======== Reload, filter and calculate additional fields ========
[As-Of Calendar]:
Load Month,
AsOfMonth,
Round((AsOfMonth-Month)*12/365.2425) as MonthDiff,
Year(AsOfMonth)-Year(Month) as YearDiff
Resident tmpAsOfCalendar
Where AsOfMonth >= Month;

Drop Table tmpAsOfCalendar;

The latest result like below.

My Expression:   SUM({$<MonthDiff ={1}>}[ Balance])     Result =250  ,600

But my expected result is 300,  300

Qlik Sense Enterprise on Windows  

@hic  , @rwunderlich , @Oleg_Troyansky , @kaushiknsolanki , @sunny_talwar ,  @MK_QSL , @marcus_sommer ,

@stevedark , @rubenmarin , @PrashantSangle 

[Aditya_Chitale]

Try:

SUM({$<Month ={" = Aggr(max(Month), [Customer Join Plan Month]) "}>}[Balance])

Regards,

Aditya

[eddywong71]

Hi Aditya_Chitale

I need Customer Join Plan Month retrieve next three month balance like below M+3

Customer Join Plan Month                    Balance

202301                                                           202304  Balance

202302                                                           202305   Balance

202303                                                           202306   Balance

How to revise the expression

 SUM({$<Month ={" = Aggr(max(Month), [Customer Join Plan Month]) "}>}[Balance]) 

[eddywong71]

Can anyone could help ?

[Aditya_Chitale]

You will need to build as of table and link it with your fact table in the script to achieve this.

Refer this link : The As-Of Table - Qlik Community - 1466130

If you still need further help,  please share the data along with expression you are using in variable ?

Regards,

Aditya

[eddywong71]

Hi  Aditya

Due to the  company policy i can't share the data. After I Refer this link : The As-Of Table - Qlik Community - 1466130.

The latest result like below.

My Expression:   SUM({$<MonthDiff ={1}>}[ Balance])     Result =250  ,600

But my expected result is 300,  300