How can I create a "month group" based on a date range manually added through parameters?
Gurus,
I need to do an analysis report based on a date range that will vary (added by the user) but wouldn't be based on "actual data" included in the report - rather I would use formulas to determine sums by month.
For example - user adds a date range via parameter 1/1/2019 - 5/1/2019
The report formula output would look like this:
Date # of Appointments # of Closed $ Closed
January, 2019 4 2 $500
February, 2019 6 1 $200
March, 2019 5 4 $600
April, 2019 2 0 $ 0
Thank in advance!
I need to do an analysis report based on a date range that will vary (added by the user) but wouldn't be based on "actual data" included in the report - rather I would use formulas to determine sums by month.
For example - user adds a date range via parameter 1/1/2019 - 5/1/2019
The report formula output would look like this:
Date # of Appointments # of Closed $ Closed
January, 2019 4 2 $500
February, 2019 6 1 $200
March, 2019 5 4 $600
April, 2019 2 0 $ 0
Thank in advance!
ASKER
mlmcc,
The client wants something like this:
Month RMR Sold Appointments Quotes Sales Ratio
Jan $803 39 27 11 28%
Feb $400 40 22 10 25%
Mar 1,447 39 32 6 15%
Apr $567 37 29 7 19%
May $752 30 19 7 23%
Jun $265 29 17 7 24%
Jul $700 40 30 7 18%
Aug $717 41 25 10 24%
Totals $5,651 295 201 65 22%
The problem is that each column is a formula that would determine if/what month it occurred. So, for instance Jan may include a "Quote" but not a "Sale", etc. So since each column could happen in a different month (and possibly be 0) I can't think of a way to "group it".
Thanks in advance
Steve
The client wants something like this:
Month RMR Sold Appointments Quotes Sales Ratio
Jan $803 39 27 11 28%
Feb $400 40 22 10 25%
Mar 1,447 39 32 6 15%
Apr $567 37 29 7 19%
May $752 30 19 7 23%
Jun $265 29 17 7 24%
Jul $700 40 30 7 18%
Aug $717 41 25 10 24%
Totals $5,651 295 201 65 22%
The problem is that each column is a formula that would determine if/what month it occurred. So, for instance Jan may include a "Quote" but not a "Sale", etc. So since each column could happen in a different month (and possibly be 0) I can't think of a way to "group it".
Thanks in advance
Steve
What does the data look like?
Can you provide some sample (even dummy data) so I can figure out something?
Can you provide some sample (even dummy data) so I can figure out something?
ASKER
Here you go Mike
Experts-Exchange-Month-Example.xlsx
Experts-Exchange-Month-Example.xlsx
That appears to be the desired output not the raw data. There is no problem with that data to produce the desired report.
mlmcc
mlmcc
ASKER
mlmcc,
My stumbling block is what do I use for a "month name" group? Normally when I create reports I would group on a date field but the raw data may not include a record for that month (or any of them)?
Steve
My stumbling block is what do I use for a "month name" group? Normally when I create reports I would group on a date field but the raw data may not include a record for that month (or any of them)?
Steve
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
You can group on formulas so long as they aren't printtime formulas.
mlmcc