Retrieving values using 'Single Date' - Rolling and Fiscal Year to date

[Ops_Expert]

Hey Quicksight experts, I have a requirement below:
Allow a user to select any date within the past 4 years (Calendar pop-up) and display 2 types of results-

1. Average of Sales - Rolling (past) 12 months
2. Average of Sales - FYTD starting April 1st of respective year to that point in time

Requirement I: For the Calendar Pop-up (Using Parameters set to today’s date as max)
I tried using Date Filter using ‘Relative dates’ & Date Range, but in either cases, the user is required to input a ‘Range’ not ‘one specific date’.
Is there a way to do this?

Requirement II: For the calculations at the 2 levels:
Is there a way to add Rolling 12 months and FYTD as calculated fields that would change based on this date? I tried 2 ways-

1. I tried the ‘set rolling dates’ selection(new feature for date filters), but by default it’s set for only Calendar start and end dates, which is not the requirement. Using windowAVG function doesn’t yield the right results (by default it’s the current date) to previous 12 months for Rolling
2. FYTD is a hurdle since, I’m not aware how to pick the first of april for the respective year, in the date selected.
   Bit frazzled at this point…

[Wakana]

hi @Ops_Expert

to get the rolling past 12 months from the specified date, you can use relative dates filter with Months for Period, Last N months for range and N=12, and then set parameter in Dates Relative to.

[Ops_Expert]

Thanks. I added a Parameter for current date and used a filter to get last the 366 days and the filters work perfectly.

There’s another issue I’m facing, after doing this the data filters out. Now I need to get the Year to Date Average -

Sample Data:

Only the highlighted green section should be averaged i.e. Value=72.48. I tried using maxOver but I’m some getting errors, unable to figure out what… here’s my query-

=ifelse(maxOver({FY #},[{Dates}],PRE_AGG),avg(Value),null)

[Ops_Expert]

Answering my own question-
I was able to derive YTD results by selecting “Top and bottom filters” and grouping by FY#. Still curious about - how can one do this as a CALCULATED Field itself instead of applying filters…

[Wakana]

if not using built-in TopN filter, you may create the calc filed to do the same like the below,
ifelse({Dates} >= {FY #},Value,null)

and then let the visual calculate the average by specifying the calc filed in the visual and set average for the aggregation.

[sagmukhe]

@Ops_Expert - I have tried to replicate your scenario at my end with an example dataset. Please follow the following calculation and let me know if this helps. Here instead of Sales I have used Quantity.

Step 1 : Apply a filter on the Visual like below to select only last 12 months data

Step 2: Create the 2 calculated fields. Calculations are given below

AvgQty-Rolling12Months

avgOver(sum(Quantity), , POST_AGG_FILTER)

AvgQtyFYTD

(sumOver((ifelse(
(extract(‘YYYY’,{Order Date}) = extract(‘YYYY’, ${paramCriticalDate})) AND
(extract(‘MM’, {Order Date}) >= 4 AND extract(‘MM’, {Order Date}) <= extract(‘MM’, ${paramCriticalDate})), Quantity, 0
)), , PRE_AGG))
/
(distinctcountOver((ifelse(
(extract(‘YYYY’,{Order Date}) = extract(‘YYYY’, ${paramCriticalDate})) AND
(extract(‘MM’, {Order Date}) >= 4 AND extract(‘MM’, {Order Date}) <= extract(‘MM’, ${paramCriticalDate})), truncDate(‘MM’, {Order Date}), NULL
)), , PRE_AGG))

Final Snapshot of the Visual using the calculated visuals:

Validation Snapshot from Excel (Chosen Date is 30th June in Parameter)

Hope this helps!

Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!

[Ops_Expert]

Treid the FYTD calculation. But it’s giving an error-

[sagmukhe]

@Ops_Expert You need to put an empty square braces as provided in the expression. In the expression the empty square braces are unfortunately looking like a square. :). Hope this helps!

[Ops_Expert]

Tried that…still not working…says Syntax of calculated field is incorrect

Here is the edited formula-
avgOver((ifelse((extract(‘YYYY’,{Date_Reg}) = extract(‘YYYY’, ${pCurrentDate})) AND
(extract(‘MM’, {Date_Reg}) >= 4 AND extract(‘MM’, {Date_Reg}) <= extract(‘MM’, ${pCurrentDate})),{Value}, 0
)),[],PRE_AGG))