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?
