
1. Re: Calc Field: Summation with Upper and Lower Bound
Dan Sanchez Feb 8, 2016 7:22 AM (in response to Erin Gehn)1 of 1 people found this helpfulHi Erin!
Hopefully I didn't misunderstand what we're going for but I think I've got a possible solution.
First off, I rewrote the Month Written to simplify the formula a bit:
[Month Written_NEW]:
SUM([No Pro])  LOOKUP(SUM([No Pro]),1)
We don't need to do the IF/THEN clauses because we can just set the scope of the table calculation to only compute within each distinct customer. I've done this by editing the advanced Compute Using settings (rightclick field in the viz > Edit Table Calculation > Advanced drop down in Compute Using).
Next, I updated the running total formula to use the following:
[Month Writting Running Total_NEW]:
IF MIN([Order Date]) >= DATEADD('year', 1, [Order Date Para])
AND MIN([Order Date]) <= [Order Date Para]
THEN WINDOW_SUM([Month Written_NEW])
END
And we'll then end up with something like this:
Let me know if this is what you were looking for.
Thanks Erin!

2. Re: Calc Field: Summation with Upper and Lower Bound
Erin Gehn Feb 9, 2016 10:52 AM (in response to Dan Sanchez)This was super helpful!!
Is there a way to only sum the monthly written new for those date criteria though?
In other words, you got the window sum to display if it exists between two date items (the parameter and order date) and this works perfectly, but the window sum is taking the sum of all dates to a customer name...not just the ones that fit the date parameter/order date criteria.
Does this make sense?
THANK YOU!!!

3. Re: Calc Field: Summation with Upper and Lower Bound
Dan Sanchez Feb 9, 2016 11:19 AM (in response to Erin Gehn)1 of 1 people found this helpfulHi Erin!
That makes total sense! I think we just need to modify the formula slightly to something like this:
IF MIN([Order Date]) >= DATEADD('year', 1, [Order Date Para])
AND MIN([Order Date]) <= [Order Date Para]
THEN
WINDOW_SUM(
IF MIN([Order Date]) >= DATEADD('year', 1, [Order Date Para])
AND MIN([Order Date]) <= [Order Date Para]
THEN [Month Written_NEW]
END)
END
first we change the position of the WINDOW_SUM to wrap around the IF/THEN statement, then we add the second IF/THEN at the beginning to only display the marks for the selected date range. We should end up with something like this:
Let me know how that looks. Thanks!

4. Re: Calc Field: Summation with Upper and Lower Bound
Erin Gehn Feb 19, 2016 11:12 AM (in response to Dan Sanchez)Your proposed solution appears to be logical. And super super close.
220+30105+95+8+3 = 9 not 7
Any idea where those other 2 are?

5. Re: Calc Field: Summation with Upper and Lower Bound
Dan Sanchez Feb 19, 2016 2:01 PM (in response to Erin Gehn)1 of 1 people found this helpfulHi Erin!
Took me a little bit of fiddling with the view but I think I figured it out. It looks like we're running into a rounding issue here. If we format the Measure Value field and show the decimal places we get a more accurate representation of the data.
Hopefully that clears things up
Thanks!