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

What if my table has date as a column? something like this
image
wouldn’t it just show those values as zero rather than actually filtering the unwanted rows out?

Hi rbrady - apologies not sure I understand. With these calculations we are trying to avoid filtering out rows because each column in the final table needs to show different periods (and hence different rows). So you are right that any rows outside of the range we define using the dateDiffs will have a value of 0, but thats what we want (becuase we might needs those rows for a different column/calculation and cant filter them out). If you actually want to filter the rows out then I would recommend using a Relative Date Filter.

Sorry for my delayed response. My goal is to have a table that shows only rows which have dates within a specific range specified by controls. However the rows that are shown are not the only ones being used for calculations. For example lets say I set my controls to only the month of May. So my table is only showing rows for 5/1/2022 - 5/31/2022. But in my table I have a column “sum of previous month” which shows me what my sales were on each day of the previous month. So even though I only want my user viewing rows with dates 5/1/2022 - 5/31/2022, I don’t necessarily want to filter out records with dates 4/1/2022 - 4/30/2022, because then my “sum of previous month” column for May won’t be able to access the values from April.

Hi Jesse, How do we find out the periodic differences when the field is a calculated field and is in percentage terms?

@Tanisha_Shetty you would need to build that ifelse logic into the numerator and denominator for the calculations that compute your percentage, then do the period over period comparison calc using those. Something like:

  • Period 1 percentage: sum(ifelse(whatever logic for period 1, numerator, 0)) / sum(ifelse(whatever logic for period 1, denominator, 0))
  • Period 2 percentage: sum(ifelse(whatever logic for period 2, numerator, 0)) / sum(ifelse(whatever logic for period 2, denominator, 0))
  • Period over period: (Period 2 / Period 1) - 1

@rbrady - I see, this is a little different because you actually want to show multiple date values in the table, along with the period over period comparisons, so your date field will wind up being part of your GROUP BY and the technique describe in this post wont work. You can acheive what you want by using Table Calculation functions to do your period over period calcs (not like the technique in this post, but rather using functions like periodOverPeriodDifference and periodOverPeriodPercentDifference, and then change your filter into a ‘hider’ by filtering on a Table Calculation version of your date field, which will cause that filter to happen later in the query pipeline and will result is keeping all your data in the query and just ‘hiding’ the data points you don’t want at the end. That last step is covered in this post:

1 Like

I see, thank you for the direction!

Thanks Jesse, I will try to implement this. :slight_smile:

Hi Jesse, if I want this to be based on a filter selection, how can I go about this? I have a governing date filter applied across the dashboard and I want the input selected there to be the base date.

Hi @ShrysNyk - You will need to use a parameter and expose a control for it rather than use a filter on this visual. You can still use the same parameter to drive the filters on your other visuals in the dashboard (when you add your date filter you will see options to have it driven by a parameter), but for the table you are building you will use the parameter in your calculated fields (like how they are written in the original post using the AsOfDate parameter).

So to be clear, you wont have any filter controls on your dashboard, only the parameter control.

Screen Shot 2022-06-24 at 8.51.42 PM

Hello Jesse, kindly see attached image. I used calculated fields to get all the figures. The figures change every day after an update.
On another page I want to use a date picker that will give me exactly these figures for the particular day (in the past) selected from the date picker. I have tried using parameter with date filter but I have not been successful. Any ideas please?
David

Hi @David_faola you dont need any date filters. Just the parameter, with a control for it, to pick the ‘as of date’ and then all the calculations in the original post will update relative to that date.

Hi @Jesse , What is ${AsOfDate}? Is this a parameter you created or something built in?

HI @ccowen - yes that is a parameter I created called AsOfDate

@Jesse Hi
I use FISCAL Periods. Each month has a different start and end date for the calendar. June for instance Starts on 4 June and Ends on 1 July
I have in my database each date of start and end.
Each month is different in number of days.
My database is updated daily with yesterday’s date, at 12 am.
I did your calculated fields for the month, quarter and year The calculation it worked Ok during June, but on July 1th everythin was reset.
Month like this ifelse(datediff({fiscalmesinicio}, now(), “MM”)= 0,{sales}, 0)

I should replace now() with something else But I’m not sure
And on the other hand, I would need to calculate the last month, quarter and annual periods of the previous year but up to date.
Can you help me?
Thanks in advance

@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 !