
Re: Why won't my calculated dimension work in an expression?
Marcus Sommer Dec 21, 2015 7:31 PM (in response to Andii Toole)
Re: Why won't my calculated dimension work in an expression?
Andii Toole Dec 22, 2015 2:26 PM (in response to Marcus Sommer )Dimensions Calculation Currently:
=IF(IF(Date(vThroughDate)>Date(DueDate) and (CloseDateFlag=0 or InvoiceCloseDate>=(vThroughDate)),(Date(vThroughDate)Date(DueDate)), 'Current')<=30,
Dual('Less than 30 Days', 30),
IF(IF(Date(vThroughDate)>Date(DueDate) and (CloseDateFlag=0 or InvoiceCloseDate>=(vThroughDate)),(Date(vThroughDate)Date(DueDate)), 'Current')>30 and
IF(Date(vThroughDate)>Date(DueDate) and (CloseDateFlag=0 or InvoiceCloseDate>=(vThroughDate)),(Date(vThroughDate)Date(DueDate)), 'Current')<=60,
Dual('31  60 Days',60),
IF(IF(Date(vThroughDate)>Date(DueDate) and (CloseDateFlag=0 or InvoiceCloseDate>=(vThroughDate)),(Date(vThroughDate)Date(DueDate)), 'Current')>60 and
IF(Date(vThroughDate)>Date(DueDate) and (CloseDateFlag=0 or InvoiceCloseDate>=(vThroughDate)),(Date(vThroughDate)Date(DueDate)), 'Current')<=90,
Dual('61  90 Days',90),
IF(IF(Date(vThroughDate)>Date(DueDate) and (CloseDateFlag=0 or InvoiceCloseDate>=(vThroughDate)),(Date(vThroughDate)Date(DueDate)), 'Current')>90 and
IF(Date(vThroughDate)>Date(DueDate) and (CloseDateFlag=0 or InvoiceCloseDate>=(vThroughDate)),(Date(vThroughDate)Date(DueDate)), 'Current')<=120,
Dual('91  120 Days',120),
IF(IF(Date(vThroughDate)>Date(DueDate) and (CloseDateFlag=0 or InvoiceCloseDate>=(vThroughDate)),(Date(vThroughDate)Date(DueDate)), 'Current')>120,
Dual('121+ Days',121))))))
I moved the above (exactly) to a Variable in order to try and use it in my expression:
SUM(IF(vAgingBuckets='Less than 30 Days',MemberBalance))
Using the Variable in an IF/SUM Statement worked to bucket the fields in each column.

Re: Why won't my calculated dimension work in an expression?
Marcus Sommer Dec 22, 2015 3:02 PM (in response to Andii Toole)Somehow it looked too complicated and I'm not sure if this expression is syntactically and logically correct. Further I think your approach to use a calculated dimensions with conditions and those conditions within your expressions goes rather in the wrong direction.
Mosten often it's enough to make those conditions an one place. This meant either to use normal dimensions with conditional expressions or to use calculated dimensions and then very simply expressions like sum(value)  then conditionally restriction of the data happens on the other side.
I suggest to simplify these (such) expressions by removing unneeded brackets, by removing date() formattings (might need adjustments on the fields and variables), by putting redundant expressionparts into variables and maybe using parametrized variables, see also Variables, by replacing nested ifloops with other logics like pick(match()), see: Re: Substitute to nested ifs and checking each expressionpart within an own expressioncolumn and if they work then put them together step by step. Even if this sounds quite inconvenient it's often faster then to develop and maintain such complex expression in one piece.
Further I would try it at first with a classical bucketsapproach with a calculated dimension and the trying to adjust them if it's really needed. I hope this give you an idea what I mean:
aggr(class(Date(vThroughDate)  Date(only({< DueDate = {"<Date(vThroughDate)"}, (CloseDateFlag = {0} + InvoiceCloseDate= {">=vThroughDate"}) >} DueDate)), 30), Member)
 Marcus

