How to calculate flu vaccination rate by year month?

[baylor2016]

Hi Experts,

I am trying to calculate the flu vaccination rates dynamically based upon user's selection in year and month. The flu vaccine is generally given from October to March. When 201608 is selected, the vaccination rate should be 33% (2015-16 season). When 201703 is selected, the vaccination rate should be 75% (2016-17 season). The green, blue and red indicate the different flu seasons. I created a small project for reference.

Thanks

id	FlushotDate	Month	YearMonth	vaccinated	Rate	Flu season
1	1/4/2016	1	201601	1	100%	2015-2016
2	2/29/2016	2	201602	0	50%	2015-2016
3	8/24/2016	8	201608	0	33%	2015-2016
4	10/13/2016	10	201610	1		2016-2017
5	11/15/2016	11	201611	1		2016-2017
6	12/12/2016	12	201612	0		2016-2017
7	12/23/2016	12	201612	1		2016-2017
8	1/11/2017	1	201701	0		2016-2017
9	2/26/2017	2	201702	1		2016-2017
10	3/4/2017	3	201703	1		2016-2017
11	3/28/2017	3	201703	1	75%	2016-2017
12	4/24/2017	4	201704	0		2016-2017
13	6/15/2017	6	201706	0		2016-2017
14	6/29/2017	6	201706	0		2016-2017
15	7/12/2017	7	201707	0		2016-2017
16	8/18/2017	8	201708	0		2016-2017
17	9/20/2017	9	201709	0	43%	2016-2017
18	10/11/2017	10	201710	1		2017-2018
19	11/12/2017	11	201711	0		2017-2018
20	12/12/2017	12	201712	1		2017-2018
21	1/2/2018	1	201801	0		2017-2018
22	2/2/2018	2	201802	1		2017-2018
23	3/3/2018	3	201803	1	67%	2017-2018
24	4/2/2018	4	201804	0		2017-2018

[sunny_talwar · Accepted solution]

May be this

=num(sum({<YearMonth= {"$(='>=' & Num#(Date(YearStart(Max(Date#(YearMonth, 'YYYYMM')), 0, 10), 'YYYYMM')) & '<=' & Num#(Date(Floor(MonthEnd(Max(Date#(YearMonth, 'YYYYMM')))), 'YYYYMM')))"}, FlushotDate>} vaccinated)

/count({<YearMonth= {"$(='>=' & Num#(Date(YearStart(Max(Date#(YearMonth, 'YYYYMM')), 0, 10), 'YYYYMM')) & '<=' & Num#(Date(Floor(MonthEnd(Max(Date#(YearMonth, 'YYYYMM')))), 'YYYYMM')))"}, FlushotDate>} id), '#,##0.0%')

[baylor2016]

Could you please provide some explanation? Thanks!!!

[sunny_talwar]

Key was to get the right range of data.... Check the below in a text box object

='>=' & Num#(Date(YearStart(Max(Date#(YearMonth, 'YYYYMM')), 0, 10), 'YYYYMM')) & '<=' & Num#(Date(Floor(MonthEnd(Max(Date#(YearMonth, 'YYYYMM')))), 'YYYYMM'))

[baylor2016]

Hi Sunny,

Can you help me with this question?

Thanks

Longmatch