display data in a bar chart for rolling 2 years

[analyticsuser]

I have a bar chart that has to display data for rolling 2 years. For 2018, it has to display count(responses) and when 2019 is selected - it has to display unique set of responses for 2018 and 2019. Do I use a calculated dimension on year or an expression would work? Please refer to the attached file. Any quick help would be greatly appreciated.

[zhaofeng]

Hi

I think you can use variable to solve it.

First,you need to set a variable named "v_year" and the variable formula is  =max(year)-2

Then,your expression is   count({<Year={">=$(v_year)"}>}responses)

You can try it and check the result

[analyticsuser]

Thanks zhaofeng. The expression doesn't work.

If I select 2018, the count (responses) is 610.

If I select 2019, the count(responses) is 730.

But, when displaying in the bar chart , for year = 2019, the user wants to display count(distinct responses) for 2018 & 2019 combined, which is 690.

How do I achieve this using set expression or calculated dimension?

[zhaofeng]

Oops,sorry,I've misread what you said

You can try this.

You need two variables.For example v_start and v_end

v_start = max(year)-1

v_end = max(year)

Your expression is   count({<Year={">=$(v_year)<=$(v_end)"}>} distinct responses)

[analyticsuser]

Hi Zhaofeng,

 The expression is not working, it displays no data in the chart.

can we try any other way?

[zhaofeng · Accepted solution]

I've made a example,you can check it.

[Vegar · Accepted solution]

I'm not able to look at your attachments at the moment, but maybe somethingllike this is what you are looking for?

Sum( aggr( rangesum( above( count(Responses),0,2) ),Year))

You could also take a look into @Gysbert_Wassenaar old blog post from 2013, Calculating rolling n-period totals, averages or other aggregations. 

[analyticsuser]

thank you for the example. When I'm trying to implement the same expression in the bar chart with dimension as Year, somehow the data is not being displayed.
I'm using the expression - count({<Year={">=$(vYearStart)<=$(vYearEnd)"}>} distinct Responses)
vYearStart = max(Year) - 1 :2018
vYearEnd = max(Year) : 2019

[analyticsuser]

Thank you Vegar. The expression works but not the way I want it to.
When 2018 is selected, it should show just the count of responses for 2018.
When 2019 is selected, the bar graph should show the combined count of responses for 2018,2019 .