Need Open Items Aging on Yearly Trend Bar Chart dynamically

mscmenon — Sat, 20 Nov 2021 18:18:08 GMT

I have a data set where the Open Items changes every month. The open Items from Open Items Table moves to Cleared Items Table as and when it gets cleared.

I want to show the open items aging in different buckets ('Current', Over 1 to 6', 'Over 7 to 30 ' etc..) on the bar chart.

On X-axis I want month-end dates for the past 12 months.

I have a dynamic input box containing the variable key date (v_Key_Date). The default value is today(), but I can change it and the graph should change accordingly.

For Dimension 1 I have the following variable (v_Monthly_ Open_Items) expression:

IF(([Posting date]<='$(v_Key_Date)' and LEN(TRIM([Cleared date] )) < 4),'$(v_Key_Date)',
IF(([Posting date]<='$(v_Key_Date)') and ([Cleared date]>'$(v_Key_Date)'),'$(v_Key_Date)',

IF(([Posting date]<=floor(monthend(addmonths('$(v_Key_Date)',-1))) and LEN(TRIM([Cleared date] )) < 4),floor(monthend(addmonths('$(v_Key_Date)',-1))),
IF(([Posting date]<=floor(monthend(addmonths('$(v_Key_Date)',-1)))) and ([Cleared date]>floor(monthend(addmonths('$(v_Key_Date)',-1)))), floor(monthend(addmonths('$(v_Key_Date)',-1))),

IF(([Posting date]<=floor(monthend(addmonths('$(v_Key_Date)',-2))) and LEN(TRIM([Cleared date] )) < 4),floor(monthend(addmonths('$(v_Key_Date)',-2))),
IF(([Posting date]<=floor(monthend(addmonths('$(v_Key_Date)',-2)))) and ([Cleared date]>floor(monthend(addmonths('$(v_Key_Date)',-2)))), floor(monthend(addmonths('$(v_Key_Date)',-2))),

IF(([Posting date]<=floor(monthend(addmonths('$(v_Key_Date)',-3))) and LEN(TRIM([Cleared date] )) < 4),floor(monthend(addmonths('$(v_Key_Date)',-3))),
IF(([Posting date]<=floor(monthend(addmonths('$(v_Key_Date)',-3)))) and ([Cleared date]>floor(monthend(addmonths('$(v_Key_Date)',-3)))), floor(monthend(addmonths('$(v_Key_Date)',-3))),

IF(([Posting date]<=floor(monthend(addmonths('$(v_Key_Date)',-4))) and LEN(TRIM([Cleared date] )) < 4),floor(monthend(addmonths('$(v_Key_Date)',-4))),
IF(([Posting date]<=floor(monthend(addmonths('$(v_Key_Date)',-4)))) and ([Cleared date]>floor(monthend(addmonths('$(v_Key_Date)',-4)))), floor(monthend(addmonths('$(v_Key_Date)',-4))),

IF(([Posting date]<=floor(monthend(addmonths('$(v_Key_Date)',-5))) and LEN(TRIM([Cleared date] )) < 4),floor(monthend(addmonths('$(v_Key_Date)',-5))),
IF(([Posting date]<=floor(monthend(addmonths('$(v_Key_Date)',-5)))) and ([Cleared date]>floor(monthend(addmonths('$(v_Key_Date)',-5)))),floor(monthend(addmonths('$(v_Key_Date)',-5))),

IF(([Posting date]<=floor(monthend(addmonths('$(v_Key_Date)',-6))) and LEN(TRIM([Cleared date] )) < 4),floor(monthend(addmonths('$(v_Key_Date)',-6))),
IF(([Posting date]<=floor(monthend(addmonths('$(v_Key_Date)',-6)))) and ([Cleared date]>floor(monthend(addmonths('$(v_Key_Date)',-6)))), floor(monthend(addmonths('$(v_Key_Date)',-6))),

IF(([Posting date]<=floor(monthend(addmonths('$(v_Key_Date)',-7))) and LEN(TRIM([Cleared date] )) < 4),floor(monthend(addmonths('$(v_Key_Date)',-7))),
IF(([Posting date]<=floor(monthend(addmonths('$(v_Key_Date)',-7)))) and ([Cleared date]>floor(monthend(addmonths('$(v_Key_Date)',-7)))),floor(monthend(addmonths('$(v_Key_Date)',-7))),

IF(([Posting date]<=floor(monthend(addmonths('$(v_Key_Date)',-8))) and LEN(TRIM([Cleared date] )) < 4),floor(monthend(addmonths('$(v_Key_Date)',-8))),
IF(([Posting date]<=floor(monthend(addmonths('$(v_Key_Date)',-8)))) and ([Cleared date]>floor(monthend(addmonths('$(v_Key_Date)',-8)))), floor(monthend(addmonths('$(v_Key_Date)',-8))),

IF(([Posting date]<=floor(monthend(addmonths('$(v_Key_Date)',-9))) and LEN(TRIM([Cleared date] )) < 4),floor(monthend(addmonths('$(v_Key_Date)',-9))),
IF(([Posting date]<=floor(monthend(addmonths('$(v_Key_Date)',-9)))) and ([Cleared date]>floor(monthend(addmonths('$(v_Key_Date)',-9)))), floor(monthend(addmonths('$(v_Key_Date)',-9))),

IF(([Posting date]<=floor(monthend(addmonths('$(v_Key_Date)',-10))) and LEN(TRIM([Cleared date] )) < 4),floor(monthend(addmonths('$(v_Key_Date)',-10))),
IF(([Posting date]<=floor(monthend(addmonths('$(v_Key_Date)',-10)))) and ([Cleared date]>floor(monthend(addmonths('$(v_Key_Date)',-10)))),floor(monthend(addmonths('$(v_Key_Date)',-10))),

IF(([Posting date]<=floor(monthend(addmonths('$(v_Key_Date)',-11))) and LEN(TRIM([Cleared date] )) < 4),floor(monthend(addmonths('$(v_Key_Date)',-11))),
IF(([Posting date]<=floor(monthend(addmonths('$(v_Key_Date)',-11)))) and ([Cleared date]>floor(monthend(addmonths('$(v_Key_Date)',-11)))),floor(monthend(addmonths('$(v_Key_Date)',-11))),

IF(([Posting date]<=floor(monthend(addmonths('$(v_Key_Date)',-12))) and LEN(TRIM([Cleared date] )) < 4),floor(monthend(addmonths('$(v_Key_Date)',-12))),
IF(([Posting date]<=floor(monthend(addmonths('$(v_Key_Date)',-12)))) and ([Cleared date]>floor(monthend(addmonths('$(v_Key_Date)',-12)))), floor(monthend(addmonths('$(v_Key_Date)',-12))),

NUll()))))))))))))))))))))))))))

For Dimension 2 (aging) I have the following variable (v_Aging) expression:

If((Date($(v_Monthly_ Open_Items),'MM/DD/YYYY')-"Netduedate">120),'Over 121',If(Date($(v_Monthly_ Open_Items),'MM/DD/YYYY')-"Netduedate">90,'Over 91-120',
If(Date($(v_Monthly_ Open_Items),'MM/DD/YYYY')-"Netduedate">60,'Over 61-90',
If((Date($(v_Monthly_ Open_Items),'MM/DD/YYYY')-"Netduedate")>30,'Over 31-60',If((Date($(v_Monthly_ Open_Items),'MM/DD/YYYY')-"Netduedate")>6,'Over 7-30',
If((Date($(v_Monthly_ Open_Items),'MM/DD/YYYY')-"Netduedate")>0,'Over 1-6','Current'))))))

For Measure I tried Many

=Aggr(SUM(TOTAL{$<[Monthly Open Items]*= {">=$(v_Monthly_Open_Items)"}>} KUSD*-1),[Monthly Open Items])

//=(Sum({$<[Posting date] ={'<=$(=max(v_Key_Date))'}>} KUSD*-1) + Sum({$<[Posting date] ={'<=$(=max(v_Key_Date))'}>} KUSD*-1))

//Sum({<v_Variable_Status = {'Open'}, Posting_month = {"$(=Year(v_Key_Date)&month(v_Key_Date))"}>} KUSD)

//MonthsStart(AddMonths(month(=max([Posting date]))-12))

//Sum({<'$(v_Variable_Status)' = {'Open'},[Posting date] = {">=$(v_Date)<=$(v_Date_To)"}>} KUSD*-1)

Is there any option to get the right amount and aging dynamically?

topic Need Open Items Aging on Yearly Trend Bar Chart dynamically in App Development

Need Open Items Aging on Yearly Trend Bar Chart dynamically