Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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])
)
Which of the two charts are we trying to fix here? What is the exact number you are hoping to see?
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!
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)))
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 😕
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])
)
AWESOME! That is exactly it!
Thank you 🙂