Hi,
I'm having a hard time figuring out how to implement a variable into my set analysis.
My variables work as expected when putting them in a table using my dimensions %dimSecurities, it all looks fine. I guess my problem could have something to do with use of AGGR
I have created a two step variable:
My first variable:
vL.PreviousDueDate1
=Max(Aggr(Max(If([Due Date] < ToDate, [Due Date])), %dimSecurities))
This returns a numbervalue per %dimSecurities in my table
My main variable then check if the prevoius date was a date where the interest were "paid", if not this second variable will look back to get the date before:
vL.PreviousDueDate2
IF(
Sum(Aggr(
Sum({<PaymentType = {'Interest'}, [Security Date] = {'$(=Date(Max(Aggr(Max(If([Due Date] < ToDate, [Due Date])), %dimSecurities)), 'dd.MM.yyyy'))'}>} $(vAmount_in_Local_or_USD)), %dimSecurities)) = 0
, Date(Max(Aggr(Max(If([Due Date] < ('$(=Date(Max(Aggr(Max(If([Due Date] < ToDate, [Due Date])), %dimSecurities)), 'dd.MM.yyyy'))'), [Due Date])), %dimSecurities)), 'dd.MM.yyyy')
, Date(Max(Aggr(Max(If([Due Date] < ToDate, [Due Date])), %dimSecurities)), 'dd.MM.yyyy'))
Note that I have excluded the '=' in beginning of this variable. Not sure if it makes any difference as I adjust for this when I'm using the variable in my set analysis:
This set analysis returns values, but the values is not coming from the correct dates, it looks like they all have the same date (most likely the oldest date, and somehow seems to be failing on the dimension)
=
If(
Sum({<[Security Date] = {"=$(=vL.PreviousDueDate2)"}
, PaymentType = {'Balance'}, _SecurityCategory= {'1'} >} $(vAmount_in_Local_or_USD)) <> 0,
Sum(Aggr(Sum({<[Security Date] = {"=$(=vL.PreviousDueDate2)"}
, PaymentType = {'Balance'}, _SecurityCategory= {'1'} >} $(vAmount_in_Local_or_USD))
, %dimSecurities))
,'')
Any thoughts on how I can keep the dimension when putting the vL.PreviousDueDate2 into my set analysis?
Best regards
Espen
