Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try:
SUM({$<Month ={" = Aggr(max(Month), [Customer Join Plan Month]) "}>}[Balance])
Regards,
Aditya
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])
Can anyone could help ?
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
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