4 Replies Latest reply on Jun 14, 2018 10:06 AM by Vinnie Ahuja

# Need to filter and summarize # of days with less than x # of rooms

What is the calculation for filtering and summarizing data by a criteria.  The data looks like this:  I need to state that during the month of May, there were 4 rooms with only 2 items.  (Eventually, I need to roll this up to a yearly total).  I think it's a level of detail calculation but I cannot get it right.  Thank you for your help.

 Date # of Rooms 1-May 2 2-May 3 3-May 2 4-May 3 5-May 2 6-May 2 7-May 3 8-May 3 9-May 3 10-May 3 11-May 3
• ###### 1. Re: Need to filter and summarize # of days with less than x # of rooms

Have you tried something like this?

if [# of rooms] <= 2 then 1 else o end

the sum of the calculation above should yield 4 for the sample data set you provided above.

• ###### 2. Re: Need to filter and summarize # of days with less than x # of rooms

Hi see the attached

I'm confused by your posts - your data has dates and "Number of rooms" but your post references number of items and number of rooms

is some thing missing or is the data number of items and dates and you want to count the number of days with 2 times?

well that is what ie attached does - the key is to convert Number of item to a dimension on use it to categorize the data

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 3. Re: Need to filter and summarize # of days with less than x # of rooms

Thank you.  When I roll up the data to the month level, I lose the information at the day level.  (I had to use a countd formula to get the # of rooms).  This is the detail of the data.  Thank you for your help.

Renelle Risley, MBA

Seattle Children's

206-987-6453  OFFICE

206-595-8798  CELL

renelle.risley@seattlechildrens.org

OFFICE  4800 Sand Point Way, Seattle, WA 98105

MAIL      M/S MB.6.520, PO Box 5371, Seattle, WA 98145-5005

WWW    seattlechildrens.org

Connect with us online:

• ###### 4. Re: Need to filter and summarize # of days with less than x # of rooms

ah yes, it was not clear that there was a [room name] field involved from your original post.  In this case, I think you were right in the need for a LOD calc.  Try something like this:

if

{ exclude [room name] : sum([number of records])} <= 2

then 1 else 0 end

Hope this helps!

Vinnie

• ###### 5. Re: Need to filter and summarize # of days with less than x # of rooms

Just a note on this, depending on what your raw data looks like, you may need to substitute your calculation COUNTD([Room Name]) for SUM([number of records]) in the calc I provided above.