Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
I'm working to do some scenario analysis and can't figure out how to do what I need!
Imagine the following simple data set:
DocumentNum | Date 1 | Date 2 | Amount |
---|---|---|---|
1 | 3-10-16 | 3-15-16 | 100 |
2 | 3-15-16 | 3-18-16 | 150 |
3 | 2-15-16 | 2-10-16 | 200 |
4 | 2-25-16 | 2-20-16 | 125 |
5 | 1-25-16 | 2-20-16 | 50 |
I want a result returned to me that adds up all of the amounts where Date 1 is less than or equal to Date 2 for whatever my selection is. So, I have been using:
=sum({1<[DocumentNum]={"=([Date 1]<=[Date 2])"}>}Amount)
This seems to work just fine- in the data set above, this gives a total of 300 from items1,2,5.
But now, I want to allow the user the ability to enter in a number of days to add on to Date 1, and then calculate a new total. I set up an input box with a variable: v_days.
What I want my formula now to do add up all of the amounts where (Date 1 + v_days) is less than or equal to Date 2. If I entered 4 as v_days, this should give me a total of 150 (from items1,5). If I entered 10 as v_days, this should give me a total of 50 (just item 5 now works).
But I can't seem to get this formula right- help!
This seems to be working:
=Sum({1<[DocumentNum]={"=(([Date 1] + $(v_Days))<=[Date 2])"}>}Amount)
This seems to be working:
=Sum({1<[DocumentNum]={"=(([Date 1] + $(v_Days))<=[Date 2])"}>}Amount)
Amazing! Thank you so much. Why could I not do that on my own???