topic Re: display data in a bar chart for rolling 2 years in QlikView

display data in a bar chart for rolling 2 years

analyticsuser — Fri, 06 Nov 2020 02:32:07 GMT

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.

Re: display data in a bar chart for rolling 2 years

zhaofeng — Fri, 06 Nov 2020 02:39:37 GMT

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

Re: display data in a bar chart for rolling 2 years

analyticsuser — Fri, 06 Nov 2020 03:12:43 GMT

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?

Re: display data in a bar chart for rolling 2 years

zhaofeng — Fri, 06 Nov 2020 03:36:26 GMT

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)

Re: display data in a bar chart for rolling 2 years

analyticsuser — Fri, 06 Nov 2020 03:57:27 GMT

Hi Zhaofeng,

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

can we try any other way?

Re: display data in a bar chart for rolling 2 years

zhaofeng — Fri, 06 Nov 2020 06:08:22 GMT

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

Re: display data in a bar chart for rolling 2 years

Vegar — Fri, 06 Nov 2020 06:34:03 GMT

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.

Re: display data in a bar chart for rolling 2 years

analyticsuser — Fri, 06 Nov 2020 07:06:30 GMT

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

Re: display data in a bar chart for rolling 2 years

analyticsuser — Fri, 06 Nov 2020 07:06:58 GMT

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 .