-
1. Re: Offsetting a date start
Shinichiro MurakamiFeb 3, 2017 3:45 PM (in response to Isa Saramago)
2 of 2 people found this helpfulThis is quite a bit tough challenge for beginners.
Whole the concept is
1. Determine the minimum (oldest) date across all the dates.
[FirstDate]
WINDOW_MIN(MIN([Date]))
2. Determine the minimum (oldest) date for respective company.
[CompanyStart]
WINDOW_MIN(MIN([Date]))
3. Calculate the difference of "years" between (2) and (1) for respective company
[Days]
DATEDIFF('year',DATETRUNC('year',[FirstDate]),DATETRUNC('year',[CompanyStart]))
4. Normalize (2) to (1) by subtracting (3) from (2)
[OffsetStart]
DATEADD('year',-[Days],MIN(DATETRUNC('year',[Date])))
Confusion is actually as you mentioned, two calculated fields are showing exactly same formula.
This is functional at Table calculation setting, because combined tale calculation can be set in the table, with different definition for respective field.
You can see the difference in this screen.
[CompanyStart]
WINDOW_MIN(MIN([Date]))
with setting re-start at every company makes the calculation results as "First Date" of each Company.
Instead,
[FirstDate]
WINDOW_MIN(MIN([Date]))
with setting no-re-starting makes the calculation results as "First Date" of all dates.
With those different settings for respective table calculations, the calculation results of
[Days]
DATEDIFF('year',DATETRUNC('year',[FirstDate]),DATETRUNC('year',[CompanyStart]))
makes different by company and as a result,
[OffsetStart]
DATEADD('year',-[Days],MIN(DATETRUNC('year',[Date])))
works appropriately.
"Datetrunc" is forcing all the dates from "yyyy/1/1" ~ "yyyy/12/31" to yyyy/1/1. which means picking up only "year" part to make the table simple.
DATETRUNC(date_part, date, [start_of_week])
Truncates the specified date to the accuracy specified by the date_part. This function returns a new date. For example, when you truncate a date that is in the middle of the month at the month level, this function returns the first day of the month. The
start_of_week
parameter is optional. If it is omitted, the start of week is determined by the data source. See Date Properties for a Data Source.Examples
DATETRUNC('quarter', #2004-08-15#) = 2004-07-01 12:00:00 AM
DATETRUNC('month', #2004-04-15#) = 2004-04-01 12:00:00 AM
Hope this explanation helps.
BTW, for other solution, using LOD is much much easier to understand the logic and easy to set for me.
[First Date Company LOD]
{fixed [Company]:min([Date])}
[First Date Overall LOD]
{fixed:min([Date])}
[Day Difference LOD]
[First Date Company LOD]-[First Date Overall LOD]
[Offest LOD]
datetrunc('year',[Date]-[Day Difference LOD])
Thanks,
Shin
-
2. Re: Offsetting a date start
Isa Saramago Feb 6, 2017 2:50 AM (in response to Shinichiro Murakami)Thank you so much, Shin. Great explanation.
-
3. Re: Offsetting a date start
Shinichiro MurakamiFeb 6, 2017 6:01 AM (in response to Isa Saramago)
Isa,
You are welcome.
And one more thing.
Could you mark my answer as correct to close this thread.
Thanks,
Shin