I'm having trouble with calculating the "Age of a Week" with the current week. I don't want to use the Week() expression, because my data uses other weeknumbers than Spotfire (changing region & location is not an option). Each day my data table refreshes so my calculated column [Age_of_Day] calculates the age of the days from the current (real) date. Now I want to calculate the age of the weeks from the real week. I have a sample of my data here:
RowID Week Day Date Age_of_Day
12 20 do 17-5-2018 8
13 20 vr 18-5-2018 7
14 20 za 19-5-2018 6
15 21 zo 20-5-2018 5
16 21 ma 21-5-2018 4
17 21 di 22-5-2018 3
18 21 wo 23-5-2018 2
19 21 do 24-5-2018 1
20 21 vr 25-5-2018 0
21 21 za 26-5-2018 -1
22 22 zo 27-5-2018 -2
23 22 ma 28-5-2018 -3
24 22 di 29-5-2018 -4
25 22 wo 30-5-2018 -5
26 22 do 31-5-2018 -6
27 22 vr 1-6-2018 -7
Now I want a new column [Age_of_Week] which calculates the difference for every row between the value of [Week] and the value of [Week] when [Age_of_Day]=0. It should be something like this:
RowID Week Day Date Age_of_Day Age_of_Week
12 20 do 17-5-2018 8 1
13 20 vr 18-5-2018 7 1
14 20 za 19-5-2018 6 1
15 21 zo 20-5-2018 5 0
16 21 ma 21-5-2018 4 0
17 21 di 22-5-2018 3 0
18 21 wo 23-5-2018 2 0
19 21 do 24-5-2018 1 0
20 21 vr 25-5-2018 0 0
21 21 za 26-5-2018 -1 0
22 22 zo 27-5-2018 -2 -1
23 22 ma 28-5-2018 -3 -1
24 22 di 29-5-2018 -4 -1
25 22 wo 30-5-2018 -5 -1
26 22 do 31-5-2018 -6 -1
27 22 vr 1-6-2018 -7 -1
Hopefully, someone can help me. I think I have to use the OVER() statement and Intersect() but I don't know how in this case. Unfortunately, I couldn't find my answer with the help of other topics.
From version 7 onward, you can use nested aggregations, so this expression should work.
First(case when [Age_of_Day]=0 then [Week] end) - [Week]