Hi,
| Date | Values | Year Target | Monthly Target | Daily Target |
| 1/1/2019 | 0 | 33642 | 2792 | 0 |
| 1/2/2019 | 45 | 33642 | 2792 | 132 |
| 1/3/2019 | 127 | 33642 | 2792 | 265 |
| 1/4/2019 | 174 | 33642 | 2792 | 398 |
| 1/5/2019 | 174 | 33642 | 2792 | 398 |
| 1/6/2019 | 174 | 33642 | 2792 | 398 |
| 1/7/2019 | 237 | 33642 | 2792 | 531 |
| 1/8/2019 | 301 | 33642 | 2792 | 664 |
| 1/9/2019 | 368 | 33642 | 2792 | 797 |
| 1/10/2019 | 419 | 33642 | 2792 | 930 |
| 1/11/2019 | 479 | 33642 | 2792 | 1063 |
| 1/12/2019 | 479 | 33642 | 2792 | 1063 |
| 1/13/2019 | 479 | 33642 | 2792 | 1063 |
I have a visualization which has the following table
The date is calculated in the load script where missing dates are added using a calendar
Values is the sum(values) which I get from a table in the database table
Yearly Target is also a value from a database table
Monthly Target is calculated within the visualization using the below formula
round(RangeSum(above(If(Day = 1 ,(BusinessDays/(max(BusinessDaysYear))*SUM(distinct Target)), 0), 0, RowNo())))
(this considers the business days and start of the month)
Daily Target is calculated using the below formula
floor(RangeSum(Above(
if(weekday(dateenrolled)='Sat' or weekday(dateenrolled)='Sun' or HolidayValue=1,0,(BusinessDays/(max(BusinessDaysYear))*SUM(distinct Target))/BusinessDays)
, 0, RowNo())))
(this considers the business days and start of the month and weekends)
The above works fine until a range of dates is selected.
Assume I have filtered the dates between 7th Jan to 11th Jan
| Date | Values | Year Target | Monthly Target | Daily Target |
| 1/7/2019 | 63 | 33642 | 0 | 132 |
| 1/8/2019 | 127 | 33642 | 0 | 265 |
| 1/9/2019 | 194 | 33642 | 0 | 398 |
| 1/10/2019 | 245 | 33642 | 0 | 531 |
| 1/11/2019 | 305 | 33642 | 0 | 664 |
I get the above table. I know the issue with the Monthly Target as I start with Day 1.
The main issue is with the Values. It changes as Rangesum considers the current filtered values.
I was thinking of moving the formulas to the Load Script and creating the table within the Load script. Is that possible?
I tried the formulas but I get an error using Rangesum and Above together in the Load Script
Would really appreciate if someone could help me out
