Jump to content

How do I create a calculated column for prior week ("dd", -*,DateTimeNow())

David Youngquist 3

Recommended Posts

I want to compare the current week (Last 7 days) to the prior week (7 days prior to current week) and have attempted to use the following calcluated columns:

Current Week (This works) =


when [end]>DateAdd("dd",-7,DateTimeNow()) then Avg([Count(emission_rate)]) OVER (Intersect([node_name],LastPeriods(7,[end])))

else NULL


Prior Week (Does not work) =


when ([end]>DateAdd("dd",-14,DateTimeNow())) and ([end]14 &

Link to comment
Share on other sites

Do you get an error or simply NULL

I assume that theLastPeriods(7,[end]) is creating the problem as no days fall within this period for the prior week. Can you tryLastPeriods(14,[end]) and see if you get any output

Do you need the LastPeriods() at all, as your already filter down the data with your condition


Maybe you can work witha binned column on the difference of now and [end] instead.

BinByEvenDistance(DateDiff('day',[end],DateTimeNow()), 7)You can then use this column in your condition and aggregation method.

Link to comment
Share on other sites

Can you please upload some sample data and clarify how you define "last week". Is last week the same as last 7 days, or does a week start every Sunday or Monday


Are you looking for an expression for your visualization or do you really need calculated columns 


The Intersect(..., LastPeriods(7,[END])) is a rolling function. It will calculate for every data row and check the last 7 periods from this row. So I don't think this function is what you want to use.


If you provide some data I'm sure that we can find the solution for you.



Link to comment
Share on other sites

I appreciate your help thus far.  Here is a .dxp with the data I'm trying to work with.  As far as your questions, I would like to have a rolling 7day average (current week) and a rolling prior 7day average (prior week) so I can track the difference.  Right now, the current week is a rolling 7 day and prior week is a rolling 14 day average.  I hope this makes sense.
Link to comment
Share on other sites

Your dxp didn't upload. Try to upload it to your original question.


But now I understand what you want to do. You want to compare the rolling average for the last 7 days to the to the same rolling average a week ago.


There are great built-in methods for rolling averages, changes over time and also for comparing (parallel/previous) periods. (Check out the aggregation methods when you use a line chart.). But what you want to do is more complex and I can't come up with a quick solution. I'll think about it when I have more time. Let me know if you solve this in the meanwhile.

Link to comment
Share on other sites

Please check the attached dxp file.

It contains a calculated column for the moving average for 7 days and a calculated column to check the moving average 7 days ago. You can use the slider to change the number of days. For more details on how toachievethis, please check the calculated columns.

For comparison the dxpcontains also the regular moving average in a line chart. This chart will not react to the slider but you can change the settings on the y-axis.

I hope this can guide you in the right direction.


PS: I couldn't open your file since I'm on 10.10 version.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...