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]
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:
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 
Yes Jesse, thanks for your clarifications 
1 Like
@Jesse great post!!
I have a question, I’m trying to do an average of a rating field for the previous same date range, for example if the date range (startDate/endDate) via filter/parameter is 2022-01-01 to 2022-03-31 the previous date range would be 2021-10-01 to 2021-12-31.
I was able to get the previous startDate and endDate period but I’m not able/don’t know how to perform the average
Any advise would be great,
Thanks
Hi @ges214 it should be something like:
avg(ifelse(last_modified>= previousStartDate AND last_modified<= previousEndDate, rating, NULL))
1 Like
@Jesse thanks for laying these out!
In my situation, I am looking for course topics completed for each respective group, however, the same results are being returned no matter the time variable (last 90 days, last 2 weeks, YTD, etc.).
Here is what I am working with in my Pivot table visual:
group_name = Name of the Group; in the Rows field well
completion_date = date stamp of when topic is completed by the user
completions = number of topic completions (aggregate: count; shown as number)
YTD and Last Two Weeks calculated fields are returning the same figures, which is the all-time topic completions instead of completions within the specific date window:
ifelse(dateDiff({completion_date}, now(), “YYYY”) = 0 AND {completion_date} <=now(), completions, 0)
ifelse(dateDiff({completion_date}, now(),“WK”) <=2, completions, 0)
@jtuten hmm looks like you have the calc syntax correct. I would expect these to definitely work. Can you maybe DM me with a screenshot? Make sure you remove any date filters from the visual too.
hi @Jesse,
Thank you for the post! Instead of the sum, I wanted to do a distinct count and I am putting the ifelse inside a distinct_count(). My formulas is like this:
distinct_count(ifelse(dateDiff(date1, $AsOfDate,“MM”)=1, ID,0))
However, I am facing some issue in this calculation. Could you help me take a look at the formulas?
Hi @kiko , sure you can send me a DM. Generally syntax looks fine but try returning NULL instead of 0. 0 will count as a value whereas NULL will not.
Hi @Jesse !
why in my calculated field: countOver(ifelse({timestamp_extracted}>=addDateTime(-1, ‘YYYYY’, ${startDate}) AND {timestamp_extracted}<=addDateTime(-1, ‘YYYYY’, ${endDate}), sessionuuid, NULL), , PRE_AGG)
fonction addDateTime is not taken into account, and how can I fix it?
Hi @elisa - maybe this is just a copy/paste issue, but looks like you have 5 'Y’s in your function and you just have just 4 (like ‘YYYY’). Is there a reason you are using countOver which is a level-aware aggregation instead of a normal Count? Maybe you can share a screenshot and some more information about what you are trying to do.
1 Like
Hi @Jesse, thanks for your interest!
In fact, I would like to have a comparaison between the period requested thougth the controls parameters and the same period one year before.
For example we could have: from 1/01/2023 to 01/04/2023 i want to show the difference with the same date one year before.
Date parameters are modifiable, my calculated field must synchronize with that.
I have tried different functions like:
countIf(sessionuuid, {timestamp_extracted}>=${startDate} AND {timestamp_extracted}<=${endDate}) - countIf(sessionuuid, {timestamp_extracted}>=addDateTime(-1, 'YYYY', ${startDate}) AND {timestamp_extracted}<=addDateTime(-1, 'YYYY', ${endDate}))
countOver(ifelse({timestamp_extracted}>=addDateTime(-1, 'YYYY', ${startDate}) AND {timestamp_extracted}<=addDateTime(-1, 'YYYY', ${endDate}), sessionuuid,NULL),[],PRE_AGG)
periodOverPeriodDifference(countOver(ifelse( {timestamp_extracted}>=${startDate} AND {timestamp_extracted}<=${endDate} AND {event}='init', {sessionuuid},NULL),[],PRE_AGG), {timestamp_extracted}, YEAR, 1)
But the result is wrong.
When I tried this fonction:
countOver(ifelse({timestamp_extracted}>=${startDate} AND {timestamp_extracted}<=${endDate} AND event='init', sessionuuid, NULL), [], PRE_AGG)
I have the right result depending on the selected date parameters but when I implement addDateTime like this:
countOver(ifelse({timestamp_extracted}>=addDateTime(-1, 'YYYY', ${startDate}) AND {timestamp_extracted}<=addDateTime(-1, 'YYYY', ${endDate}), sessionuuid, NULL), [], PRE_AGG)
I get this error message on my visual:
I think it comes from my calculated field but I’m not sure.
But also I don’t understand why when I apply this function:
countIf(sessionuuid, {timestamp_extracted}>=${startDate} AND {timestamp_extracted}<=${endDate} AND in(event, ["init"]))
my result is wrong and when I apply this one:
countOver(ifelse({timestamp_extracted}>=${startDate} AND {timestamp_extracted}<=${endDate} AND event='init', sessionuuid, NULL), [], PRE_AGG) it is right?
ps: the filter part on the event=‘init’ is optional I have the possibility to add a filter on the visual.
Thank you for your explanation and sorry for my bad English! 
1 Like
Hey @Jesse, thank you so much for this post. It’s helped me tons so far.
In your examples, how would you approach trying to get the count of sales (number of sales) rather than the sum of sales? For now, I made a calculated field called “Count” that is set to 1…I am using that in lieu of “sales” and it seems to work but I wonder if there’s a better way?
@joeyu629 Great to hear! Yes returning a 1 instead of a field and then summing that should work, or you could return a field (either sales or the ID) and then when you drag it into your field wells just change the aggregation to Count.
Hi @elisa - sorry for the delay in responding to you. I think the approach you are doing with the countIf’s is the way I would do it. You said the results are wrong with that approach - can you elaborate more?
I had built this example in DemoCentral which seems to be the same use case. The only difference with yours is that you are returning sessionuuid instead of sales, and then in the YoY calculations you should use count instead of sum. Can you try this approach and see if it works?
https://democentral.learnquicksight.online/#Dashboard-TipsAndTricks-Calculation-YoY-using-parameters
@Jesse I am not following. What is this —> ‘${AsOfDate}’ ?