Dynamically changing dimensions, via a slider variable?

Tue, 09 Oct 2012 18:13:57 GMT

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.

Re: Dynamically changing dimensions, via a slider variable?

Wed, 10 Oct 2012 14:16:27 GMT

I figured it out. I just use a Calculated Dimension.

DATE(IF(ISNULL(InvoiceSentDt), EffectiveCheckingDt + vifSentDtNull, EffectiveCheckingDt), 'MM/DD/YYYY')

topic Dynamically changing dimensions, via a slider variable? in QlikView

Dynamically changing dimensions, via a slider variable?

Re: Dynamically changing dimensions, via a slider variable?