Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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%')
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%')
Could you please provide some explanation? Thanks!!!
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'))