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

[David Youngquist 3]

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) =

case

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

else NULL

END

Prior Week (Does not work) =

case

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

[Fabian Duerr]

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.

[David Youngquist 3]

Either way (using Last,7 / Last,14, or removing LastPeriods) I am getting NULL.  I would like to use current week, prior week and the delta on a single visualization, so I'm not sure binning by date does what I want.

[Fabian Duerr]

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.

 

 

[David Youngquist 3]

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.

[Fabian Duerr]

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.

[David Youngquist 3]

reloaded .dxp

[David Youngquist 3]

I tried the following but am still getting nulls.case  

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

else NULL

ENDWere you finally able to see the .dxp I uploaded last week

[Fabian Duerr]

No, I can't see your dxp. Do you see the dxp You have to upload the file to your original question. You can't upload it in the comment sections. It's not an allowed file type.

[Fabian Duerr]

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.