13 Replies Latest reply on Mar 30, 2020 1:22 PM by Hari Ankem

# Showing YOY change based on filter

Hi,

So I have a workbook that shows the average time it takes to go through a process and the dashboard is filtered by fiscal year. I want to show a comparison of the timeliness metric to the previous fiscal year based on the year chosen in the filter. I created a sample workbook with superstore data. In the sample workbook I show the average ship time by state and the year filter is set to 2018. In this example I need to write a calculated field that will show whether the average ship time is faster than the previous year (2017). The comparison should change based on the year chosen in the year filter. Can anyone help?

• ###### 1. Re: Showing YOY change based on filter

See if this helps:

• ###### 2. Re: Showing YOY change based on filter

Hmm I'm not sure it worked for me. Everything is showing lower even though one is higher than the previous fiscal year. This is the calculation I used:

IF { FIXED [Division]: MIN(IF YEAR([Entry Date]) = {MAX(YEAR([Entry Date]))} THEN

[Entire Duration] END )}

> { FIXED [Division]: MIN(IF YEAR([Entry Date]) = {MIN(YEAR([Entry Date]))} THEN

[Entire Duration] END )}

THEN "Higher"

ELSE "Lower"

END

I used Year because the field is a full date even though I'm using the year as the filter. Could that be the problem? I'm also trying to compare the average duration for each division. Does this calc do that?

• ###### 3. Re: Showing YOY change based on filter

If you have applied a filter, then please add that to context by right-clicking on it and check. If that still doesn't get you the desired result, please attach the updated workbook and I can check.

• ###### 4. Re: Showing YOY change based on filter

I attached a new workbook that sort of replicates what I have going on.

• ###### 5. Re: Showing YOY change based on filter

Does this look correct? Note that I have added the first 2 filters, Type Set and Step Set to context.

• ###### 6. Re: Showing YOY change based on filter

I think the problem in mine is the calculated field is not based on the average duration in my real workbook. So I tried this

IF { FIXED [Division]: MIN(IF YEAR([Entry Date]) = {MAX(YEAR([Entry Date]))} THEN

Avg([Duration]) END )}

< { FIXED [Division]: MIN(IF YEAR([Entry Date]) = {MIN(YEAR([Entry Date]))} THEN

Avg([Duration]) END )}

THEN "Higher"

ELSE "Lower"

END

But when I do that it obviously says I cannot mix aggregate and non-aggregate in an IF statement.

• ###### 7. Re: Showing YOY change based on filter

You are doing an additional average of the duration, and that's causing the issue. What is the requirement exactly?

• ###### 8. Re: Showing YOY change based on filter

In the real  data set there are many more rows for each year, so in your original calculated field it doesn't appear to compare the averages of each year. Also, the real data set has four years. So will using Max and Min of the Entry Date work if I want to compare one year to only the previous year (ex. 2019 to 2018)? I'm not sure if those are the issues, but those are the differences in the fake data and real data. I guess I could improve the fake data set so it's closer to the original.

• ###### 9. Re: Showing YOY change based on filter

Yes, you could provide data which is more similar to the original data set. Alternatively, if you really need to use the average and use it in the calculation, you can compute that too as a LOD and use that. That will take care of the aggregate error you are getting.

• ###### 10. Re: Showing YOY change based on filter

Okay I uploaded a new version with a more similar data structure. You'll see what I mean. If you look at Division E in FY20 it has an average duration of 64 days and is showing as higher than FY19, but in FY19 the average duration for Division E was 67 days. So in FY20 Division E should show as lower. Also if you choose FY18 divisions are showing as higher and lower despite having no data from FY17.

• ###### 11. Re: Showing YOY change based on filter

You will need to select both FY19 and FY20 and add them to context if you want those values to be compared. If you don't select FY19 and add/not-add the filter to context, it will be comparing FY18 and FY20 values since those 2 years correspond to the lowest and highest years in the data.

• ###### 12. Re: Showing YOY change based on filter

So maybe using a parameter is a better way to do this? Because my dataset has 4 years and I won't always be comparing everything to the max year in the dataset. I tried creating a parameter and basing the calculated fields off of that, but I'm having difficulty because my date field is set to fiscal year where the fiscal year starts on October 1.

• ###### 13. Re: Showing YOY change based on filter

See if this helps: