Can anyone help me with the proper syntax for using the DDIFF function to calculate how many days it has been between any specific date and today?
Look at the Functions tab to see the syntax for each function.
For "today", use the NOW function.
If you were computing the date difference between today and the performance date, you would use this:
DDIFF ([Days in Date], NOW([Days in Date]))
Now for the weird part, it seems to matter what kind of data you have in a pivot table. If I have text data, I need to divide the formula by 4 in order for it to compute correctly.
If I have Date data, the formula computes correctly without the divide by 4 part.
If you add numeric data, you get an error.
However, if you add an additional Value, it will compute correctly without the divide by 4 or an error.
Well that was fun.
Thanks so much for the screen shots - I was just not connecting what to use for "<Start Date>" etc in the DDIFF() function and rather than spin my wheels, I figured I'd ask out here, where someone was bound to know it off the top of their head. And lo, the Tessitura Community comes through again. Thank you!
P.S. Did exactly what I needed to do with that and in way less time than it would have taken for me to fumble through it myself. THANK YOU NEIL!
Hi Mindee and Neil,
For best results, apply an aggregation to the results of the DDIFF function in uses such as those listed above. For example:
MAX ( DDIFF ( [Days in Date], NOW ( [Days in Date] ) ) )
That should remove the apparent need to divide by 4, as well as the error when grouped by Constituent ID. Without the aggregation the formula is in essence resulting in multiple values being returned per row, and then having to figure out what to do with that to still display those results within one row. It should really throw an error that the result of the formula without an aggregation is a dimensional value rather that and aggregate, and not let you save it... future fix.
Thanks, Chris, for that awesome explanation. I'm a little afraid to tell y'all that the widget for which I need this function is not remotely sophisticated enough for the aggregation to be necessary