Rangesum with time criteria

[data_RN]

Last week I posted a question regarding a range sum with varied requirements, of which I got the answer for (thank you!) How can I remove a dimension from a table when it is part of a calculated expression? and now they are wanting to limit the programs included by adding a time limit.

I need to include the Program IF there will also be a "Go-To-Stage Meeting Date" in the next two months.

I have been trying to figure it out, but I can only manually calculate if there is a meeting date in the next months by each stage (my example is in the attached file), but I can't figure out how to incorporate it into the existing RangeSum calculation. Below is sorta what I would like - I know, the syntax is totally wrong 😊

Is there an easy fix for this?

RangeSum(
 Count({<[Go-To Seed List Done] = {'Yes'}, [Stage] = {'Seed'}, [Go-To-Seed Meeting Date] = between Today() and Today()+60}>}[Program]),
Count({<[Go-To-Alpha List Done] = {'Yes'}, [Stage] = {'Alpha'}>}[Program]), [Go-To-Alpha Meeting Date] = between Today() and Today()+60}>}[Program]),
Count({<[Go-To-Beta List Done] = {'Yes'}, [Stage] = {'Beta'}>}[Program]) [Go-To-Beta Meeting Date] = between Today() and Today()+60}>}[Program]),
)

Thanks again in advance!

[sunny_talwar]

Which of the two charts are we trying to fix here? What is the exact number you are hoping to see?

[data_RN]

Hi Sunny, 

I am looking to see the number of programs that have completed their list for that stage AND have a meeting date in the the next 2 months for that stage.  So something like this:

Group	Count (Program)	Combo -  List done for current stage	List done for current stage AND has a meeting planned in next 2 months
	9	5	2
a	3	2	2
b	3	1	0
c	3	2	0

Is that clearer?

Thanks for your help!

[sunny_talwar]

Why can't you just use this same expression from the above chart in the below chart?

(Count(If([Go-To Seed Meeting Date] >Today(),0)))-(Count(If([Go-To Seed Meeting Date] >Today()+60,0)))

 

[data_RN]

Hello again, 

It only gives me part of the answer.  I need the sum total using the current stage.  I have only figured out how to do it by one stage at a time.

Here is what I mean:

Group	Stage	Count (Program)	List Done for Current Stage	Meeting Dates within 60 days for Seed Stage Only	Meeting Dates within 60 days for Alpha Stage Only	Meeting Dates within 60 days for Beta Stage Only	Current Stage, List Done IN Time Period  for Current stage - THIS IS WHAT I NEED
		9	5	2	2	1	2
a	Seed	1	1	1	0	0	1
a	Alpha	1	1	0	1	0	1
a	Seed	1		1	0	0	-
b	Beta	1		0	0	0	-
b	Seed	1	1	0	1	0	-
b	Alpha	1		0	0	0	-
c	Beta	1		0	0	0	-
c	Beta	1	1	0	0	0	-
c	Alpha	1	1	0	0	1	-

I updated the file with the above and attached.  The last column is the one I can't figure out.  I had hoped I could alter your original RangeSum formula to include the time period check for the Go-To- "X" Meeting Date, but I can't seem to get it to work 😕

[sunny_talwar · Accepted solution]

I am still not 100% sure I understand, but try this

RangeSum(
	Count({<[Go-To Seed List Done] = {'Yes'}, [Stage] = {'Seed'}, [Go-To Seed Meeting Date] = {">=$(=Date(Today()))<=$(=Date(Today() + 60))"}>} [Program]),
	Count({<[Go-To-Alpha List Done] = {'Yes'}, [Stage] = {'Alpha'}, [Go-To-Alpha Meeting Date] = {">=$(=Date(Today()))<=$(=Date(Today() + 60))"}>}[Program]),
	Count({<[Go-To-Beta List Done] = {'Yes'}, [Stage] = {'Beta'}, [Go-To-Beta Meeting Date] = {">=$(=Date(Today()))<=$(=Date(Today() + 60))"}>}[Program])
)

[data_RN]

AWESOME!  That is exactly it!

Thank you 🙂