I am trying to find the difference between two dates in quicksite using dateDIFF. Basically it would just be identifying when there is a change in a “launch” date month over month. However, it doesn’t seem to be reflecting any deltas. Any idea of what I’m doing incorrectly?
dateDiff({Accounting Lease Commencement Date}, {Accounting Lease Commencement Date},“MM”)
Can you be more specific by supplying an example with specific dates?
Hi @Andy_Brand,
In my columns field, I have a project start date for different project names (project names being the rows). Each month I get an updated project start date for each of the different projects (my columns represent each monthly report). Most of the times there’s no variance in the project start dates but occasionally there is and that’s what I want to flag with the dateDIFF. I’ve put the dateDIFF as above with the return in MM. However, all of the calculations are returning 0.
So for example, I have project X that is supposed to start on 6/1/2023. However, the following month I see that the report shows that the project is supposed to start on 8/1/2023. I would expect for there to be a 2 in the calculation field but it’s showing 0.
Are you using one of the table functions to access the previous value. You probably want to use lag, but without knowing more specifics it’s hard to know for sure.
Here is a simple example.
Let’s say you had a 2 Col table like so
Project ReportDate StartDate
A 12/1/22 6/1/23
B 12/1/22 6/1/23
A 1/1/23 8/1/23
B 1/1/23 6/1/23
And a pivot table with Project in the row and Stare dat in the column
dateDiff (StartDate, lag(StartDate, [ReportDate ASC], -1, Project), “MM”)
Hello @lambsn, The expression that you are using i.e. dateDiff({Accounting Lease Commencement Date}, {Accounting Lease Commencement Date},“MM”) will always return zero only as you are trying to find difference between same date. Do you have 2 different columns like Planned start Date and Actual start Date? If yes, then you should use the datediff function between these 2 dates. A sample snapshot of your dataset and visual will really help to replicate the scenario at our end and respond more accurately. Thank you!