Re: How do you create a calculated column that subtracts multiple date fields?
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:
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