I have a dataset that contains accounts and their corresponding values over several years. I want to be able to create a date filter that will show me the the % change based on the the two dates selected. For instance, if the Start Date filter is set to January 2020 and the End Date filter is January 2021, I want to see that the account value grew by 10%. I am not sure if using two date filters is best or creating a Max/Min calculation for a between date filter is best. Any insight would be appreciated.
I would use two parameters and a calculated field.
The calculated field would be this:
sum(ifelse(truncDate('MM',{date})=truncDate('MM',${StartDate}),{value},NULL))/sum(ifelse(truncDate('MM',{date})=truncDate('MM',${EndDate}),{value},NULL))
That would be just the ratio.
However for a percent difference you would just add a subtraction to the numerator and change the denominator to start date.
1 Like