How do you create a calculated column that subtracts multiple date fields?

[scheibercamo]

Overall, I am unfamiliar with subtracting date fields from one another, much less manipulating them in a set analysis with multiple conditions; much help is appreciated!

I have three date fields:

Date 1

Date 2

Date 3

And a Firm_ID field...

What I want to express, essentially as an "IFERROR" like I can in excel...

If a date in Date 2 occurs before it does in Date 1, I want it to display the count of Firm_IDs that meet that union

Sometimes, however, Date 2 has null values. In that case, I want the number of Firm_IDs for which Date 1 occurs before Date 3 (both Date 1 and Date 3 fields will always be non-null).

Any pointers? Do I need to do this in the load script? Am I overcomplicating this?

[JustinDallas]

You can do this in the Load Script, or the UI.  Personally, I like to put such calculations in the Load Script.  Let's assume we have a a list of Biscuits and we want to know when the biscuit was eaten because some of them are stale.  Below is an example script, but what you care about is the line here:

LOAD *, If( IsNull(BiscuitStaleDate), 'Not Stale' , 
If( Interval(BiscuitEatenDate - BiscuitStaleDate) > 0,
            1, 'Not Stale')
            ) AS YouAteAStaleBiscuit

Here, I am saying that "not every biscuit has a BiscuitStaleDatedate. If it does have a BiscuitStaleDatedate, make sure the biscuit was eaten before the BiscuitStaleDate.  If the biscuit wasn't eaten by the BiscuitStaleDate, output a '1'.  The staleness is ONLY computed for biscuits that have a BiscuitStaleDate, otherwise I output 'Not Stale'. 

Then, in my UI I can use the following for my dimension and I de-select the "Show Null Values" box.

If( YouAteAStaleBiscuit = 1, BiscuitId, Null())

Here is the script to generate test data, keep in mind, it uses Rand, so data won't be repeatable between runs

MyBiscuits:
LOAD *, If( IsNull(BiscuitStaleDate), 'Not Stale' , 
               If( Interval(BiscuitEatenDate - BiscuitStaleDate) > 0,
                      1, 'Not Stale')
                      ) AS YouAteAStaleBiscuit
;            
LOAD RowNo() + 0 AS BiscuitId,  
If( Rand() > 0.5, 
MakeDate(2013, Ceil(Rand() * 12), Ceil(Rand() * 29))) AS BiscuitStaleDate,
    MakeDate(2013, Ceil(Rand() * 12), Ceil(Rand() * 29)) AS BiscuitEatenDate
AUTOGENERATE 20;    


Exit Script
;