Period Over Period and Other Date Comparison Calculations (YoY, MoM, YTD, Rolling 90 days, etc)

@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 :slight_smile:

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.

If this is about the calc, you can write a calc for last 4 months from the examples in the original post (see example for Last 2 Weeks and adjust it). If this is about the header, you’re right in the current state there isn’t a way to set a default parameter value for a trailing 4 months.

Understand, Thanks for the clarification.

Hey Jesse - I am trying to follow your method of creating such a table visual but I am running into some errors when I use a count aggregator instead of a sum.

Here is some background info:
Date var = local_timezone_payment_records
Date parameter = EndDate (which is a relative date set to Today)
Unique Payment ID = id[table_session]

image

This is the formula for Transactions (Yesterday):

And it is the conversely the same for Transactions (Last week) with a time delta of -8.

I have applied a count table aggregation to both metrics for the table visual.

The issue here is that the WoW change is showing incorrect values.

Here is the formula:

Kindly seeking your assistance. Thanks!

Hi @sahil.maisuria - looks strange. So both transactions columns are showing the right numbers but just the WoW Change is wrong? Your formula looks correct for the WoW. Are you doing a Distinct Count on the transactions columns but a Count on the WoW by chance?

Hi Jesse,
I have a question for using function dateDiff in QuickSight – I have different results for formula “dateDiff({Date},now(),“YYYY”)” in defining calculated field in Dataset and in defining calculated field in Analysis. In our opinion, result of calculated field defined in Analysis is correct (that is, e.g. Dec. 20 is previous year).
Example:
image002

Thank you for your explanation

The main difference (assuming you are using SPICE) is that when you build it in the dataset, your database is computing that result whereas in the analysis, SPICE is computing the result. It seems to me that the data source you are using interprets this query at the day level (like in a rolling 365 day type of year definition) whereas SPICE is solely looking at the year value itself. Hard to say one is wrong and one is right, they are just different. I agree it is not what I would expect the data source to do, but each source is a bit different :slight_smile: