Why won't my calculated dimension work in an expression?

[#1]

I am trying to calculate Accounts Receivable Aging by bucketing my overdue amounts at a specific point in time in increments of 30.

So, if I input a variable date field, it will tell me the amount overdue. I need  it to look like below:

Member	Original Amount	Total Due	1-30 Days Overdue	31-60 Days Overdue	61 + Days Overdue
Bob	$100.00	$50.00	$0.00	$50.00	$0.00
Sheila	$500.00	$300.00	$100.00	$100.00	$100.00
Carlos	$300.00	$10.00	$10.00	$0.00	$0.00

My calculated dimensions is as follows (to calculate the days overdue in a raw data table):

IF(Date(vThroughDate)>Date(DueDate) and (CloseDateFlag=0 or CloseDate>=(vThroughDate)),(Date(vThroughDate)-Date(DueDate)), 'Current')

Basically, vThroughDate is my variable date field you can enter to get a specific point in time. If that date is greater than the Due Date and the invoice is either not closed, or it was closed after my due date, then calculate the days overdue, otherwise call it "Current".

This works as a dimension in my table, but when I try to use it in the expression to create my buckets - it doesn't work. It simultaneously attributes every amount to the Total Due, the Current Due, and the last bucket. But not the buckets in between. Those expressions look like this:

IF(IF(Date(vThroughDate)>Date(DueDate) and (CloseDateFlag=0 or CloseDate>=(vThroughDate)),(Date(vThroughDate)-Date(DueDate)), 'Current')<=30,

SUM(BalanceDue))

Why does the expression work as a calculated dimension in one table - but not in the expression of another? And how should I fix it?

[marcus_sommer]

This one Buckets had similarities to your task and might give you some hints to solve it.

- Marcus

[#3 · Accepted solution]

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.

[marcus_sommer]

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 expression-parts into variables and maybe using parametrized variables, see also Variables, by replacing nested if-loops with other logics like pick(match()), see: Re: Substitute to nested ifs and checking each expression-part within an own expression-column 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 buckets-approach 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