@Jesse gotcha, this example seems a little different than what I’m looking for. I want to display a rolling average in a time series line graph. Can I create a calc for that?
@ccowen Rolling average in a time series will use the runningAvg() function. Would look like this:
runningAvg(sum(sales), [{your date field} ASC], [any partition fields you need, for instance if you have a field on Color, or can leave this empty])
@sanmar I replied to your DM you sent me. Since you have fiscal periods your calculations will look different (you will compare the dates directly against your fiscal period start/end date columns - using the dateDiff functions wont work since those measure in normal calendar periods).
@Jesse thanks for pointing that out. Those are all of the parameters for runningAvg(), so I cannot specify the period to average. eg, I want to average rows with a OMdate within the last 30 days, not skipping days that might not appear in OMDate. I want this rolling average for a few years, so it can be plotted as a time series
@Jesse hi Thank you!!
the monthly function ifelse(fiscalmesfin = maxOver(fiscalmesfin, [], PRE_AGG), cajasfact, 0) worked OK. This means that it will accumulate until the end of the month (Aug 5)? and on August 6 the new month begins Ok ?
I tried the function for the Month ( previous year) Month to Date, ifelse ({fiscalmesfin} = addDateTime(-1, “YYYY”, maxOver({fiscalmesfin},[], PRE_AGG)) , {sales}, 0) but it doesn’t work. Results in zero
Finally regarding the quarter: my database does not handle start and end periods for the Quarter to make the calculation. What could the calculation be?
Thanks in advance !
@ccowen Sounds like you are looking for windowAvg(), which you can specify the lookback period in it. Would you mind posting a new question or sending me a DM since we are on a different topic than this original article is meant to address. Thanks!
@sanmar Going to reply to our DM thread again since your situation is unique. Lets continue to conversation over there.
Hi Jesse, thanks for a great summary of how to work with dates. I want to do something similar to this. I want to do a distinct count of agreements numbers MTD and YTD. I have tried using the MTD suggestion above but does not seem to work. Any thoughts?
Hi All,
Looking to create two separate visuals comparing the current financial year (5th April - 4th April) to the previous financial year.
This will be for both spend and shifts filled ideally, is there a way to produce a calculated field which would be able to show this?
Thanks
Jake
Hi @DrMarten - if you return the field you want to do a distinct count of (rather than ‘sales’ like in the examples), and then wrap the entire formula inside a distinctCount() function then it should work.
Hi @JakeMcK1995 these wont work well for fiscal periods. You will need to define your time periods manually, but the same concept of using ifelse() applies. Answet will depend on if you need these to be dynamic (should it update when you hav ea new year of data) and if your fiscal periods dates are always the same (for previous year is it also April 5 to April 4?). To better help you out, could you post your question as a new topic and we can answer it separately please?
@Jesse I created a new question, can you take a look? Rolling Average calc for use in line chart
Hello @Jesse
Thanks for sharing the summary!!
I am working on a dataset that has multiple metrics like sales, cost, revenue, customers…I can create multiple calculated fields and derive MoM%/YoY%. But I would like to know if there are any other easier ways to have MoM% calculations at one go.
In the above solution, I see that it only refers to a singe metric i.e., sales. Can we create a single calculated field to calculate MoM% /YoY% for multiple metrics.
Appreciate your help!!
Thanks
Hi @sushma.sreella - the only way to do this for multiple metrics at once is if you were to reshape your data so each metric is a row instead of a column. Your data set will be much skinnier but longer. It will reduce the amount of calcs you need to write, but you wont be able to have different formatting for each metric (since all metrics will be in a single field called Metrics and you will have another fields called Metric Name, or something similar). This is referred to as ‘unpivoting’ data in data prep world. You can do it in Glue DataBrew and Im sure other 3rd party data prep tools. There are pros and cons to it 
This is really great. Thank you.
Is there anyway we can show actual Date/Month/Week instead of naming it as “This Month”/“Last Month” ?
Hi @vikas_n - there is a way to get a full date in there in YYYY-MM-DD format by creating parameters that use Relative Date defaults to dynamically update to things like today, or start of this month, or start of previous month, etc.
You can then use these parameters inside column headers, however in this scenario we wouldn’t be able to change the format of it to only display MM-YY for instance.
Hi Vikas,
Please let me know if you get a response. I am also looking for something similar kind of help
@sasikanth see above
Thanks Jesse. This is really helpful. Appreciate you reply.
But can we get last 4 months data in the parameters? I can see only “This” and “Previous” values are available in the dropdown. I wanted to plug last 4 months data. As per the above logic, I can define only 2 columns of the table. Please let me know, if this can be achieved in some way.
Thanks,
Vikas
Thanks Jesse. Will explain the same to the team.