How can I aggregate the denominator to achieve this so that I get the correct answer even if a selection is made?

[#1]

Hi Everyone,

I am new to Qlikview. I looked through several threads but could not find solution to this:

I have data in a similar format:

Month	# Hired	# Hired till today	# Active Currently	#Active/ # hired till today
1	5	5	3	3/5 = 60%
2	7	12	10	83%
3	2	14	9	64%
4	4	18	15	83%
5	9	27	25	92%

And I also have #hired by different managers, branches etc like this:

Month	Manager	#Hired
1	A	3
1	B	2
2	A	4
2	B	2
2	C	1

and so on for all the months..

I would like to calculate '#Active/ # hired till today'. And to some extent, I have already achieved this but the problem that I am facing is that I don't get the correct values if any filter is applied. For eg if I look at the same % by different branches and managers on a monthly basis, I am not getting the right answers. I would really appreciate it if you can help me with this.

Also, just an additional note, I am calculating this in a chart with Month as X-axis and %Active/hired as Y-axis.

Thanking you in advance,

Roopam

[Gysbert_Wassenaar]

Try: rangesum(above(sum([# Active Currently]),0,rowno()))/rangesum(above(sum([# Hired]),0,rowno()))

[#3]

Hi Gysbert,

thank you for your reply. Few follow up questions:

1. If i make any selections on month.. i still want the cumulative from the first month, i.e. from the begining irrespective of the selections.

2. My two fields : # hired & # Active correspond to 2 different date fields. # hired corresponds to hire date while #active relates to the transaction date. How can i show the ratio on a line chart with a common date field?

Thank you in advance!

[Gysbert_Wassenaar]

1. See this blog post: Accumulations‌

2. By using a common date field. You cannot use two dimensions as one chart dimension.