Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Bare with me, hopefully you can follow the logic. I have a pivot table that looks like this:
The Sent Date expression is this:
DATE(IF(ISNULL(InvoiceSentDt), InvoiceDt + vifSentDtNull,InvoiceSentDt), 'MM/DD/YYYY')
Check if InvoiceSentDt is NULL, then Sent Date = InvoiceDt + vifSentDtNull. This variable is controlled by a slider. This works fine.
This is what I'm having issues with: I want to be able to pass on this variable to Effective Checking Date (the dimension).
This is the load script for Effective Checking Date:
ISNULL(ActualPaymentDt, // If ActualPaymentDt exists, use that instead.
DATEADD(day,
ISNULL(a.Term,0) + ISNULL(c.ClientRiskAdjs,0) + ISNULL(b.InvoiceDateAdjs,0),
ISNULL(InvoiceSentDt, InvoiceDt)
) AS EffCheckingDt
So, what's going on here... Effective Checking Date = ActualPaymentDt (if it exists)... Else, calculate it. = SentDt (if exists) + Term of the payment + client-wide date adjs value + invoice-specific date adjs value.
My issue is bolded in red. Some invoices aren't sent yet, but I want to be able to predict this too. SentDt, assume to equal InvoiceDt + slider value.
So, how do I pass the slider variable onto the EffCheckingDt calculation? Thanks.
I figured it out. I just use a Calculated Dimension.
DATE(IF(ISNULL(InvoiceSentDt), EffectiveCheckingDt + vifSentDtNull, EffectiveCheckingDt), 'MM/DD/YYYY')
I figured it out. I just use a Calculated Dimension.
DATE(IF(ISNULL(InvoiceSentDt), EffectiveCheckingDt + vifSentDtNull, EffectiveCheckingDt), 'MM/DD/YYYY')