7 Replies Latest reply on Aug 30, 2017 11:56 AM by lisa.brisenoalaimo

# Check if record meets certain criteria then sum those records

Hi again,

Thank you Jennifer VonHagel for the Sum tip yesterday, today I realize that each of the 3 sums I am working with must display only certain TicketTypes.

I have 2 TicketTypes in the data and need to be able to identify TicketType1 and provide a sum of those records meeting the conditions below:

sum(IF DATEDIFF('second', [Created], NOW()) >= 2592000

THEN 1 END)

I cannot use filters as I am showing 3 different calculations on a sheet and to filter out one TicketType in one filter will filter the records from another calculation.

#### What would I enter into my calculation to only apply  the calculation to TicketType1 and display only those records?

I am unable to attached a table due to work policy. I can try to extract a portion and change some data if this would be more helpful in visualizing what I'm trying to do.

Thanks, Lisa

• ###### 1. Re: Check if record meets certain criteria then sum those records

You can add additional AND clauses inside the IF logic inside the SUM.

As for displaying only TicketType1 without filtering out the other rows from the underlying table, do this:

LOOKUP( IF ATTR([TicketType]) = "TicketType1" then 1 ELSE 0 END) ,0)

LOOKUP is a table calc.  When you use a table calc as a filter, it leaves the underlying table intact and just controls what portion of the table is displayed.

I typed that calc off the top of my head, so you may encounter a syntax error.  If I had a workbook to play with I could have tested it out.

That calc says:  for each row (that what the ",0" in the second argument does) check the value of [TicketType].  Set 1 or 0 accordingly.  Put that calc on the filter shelf and select for value = 1.

• ###### 2. Re: Check if record meets certain criteria then sum those records

Hi Lisa, if you can put some fake data in a .TWBX, or mockup in Excel how you want the end state of your view to turn out, that would help. I'm not really clear how you want to display the results, and that will determine what best answer to suggest. But, in general, you can just add conditions to your if statement...

Best,

Jennifer

sum(IF [Ticket type]='TicketType1' AND DATEDIFF('second', [Created], NOW()) >= 2592000

THEN 1 END)

• ###### 3. Re: Check if record meets certain criteria then sum those records

Hi, I've attached a workbook to my original post.

Thanks again, Lisa

• ###### 4. Re: Check if record meets certain criteria then sum those records

Hi, I've attached a workbook to my original post.

Thanks again, Lisa

• ###### 5. Re: Check if record meets certain criteria then sum those records

I changed [Open >30 Days] to count only [Task Type] = "Incident".

See attached.

• ###### 6. Re: Check if record meets certain criteria then sum those records

Hi Lisa, it looks like Joe has attached a workbook with the additional condition added to your metric .

If you have any other questions, feel free to reach out, happy to help.

Have fun vizzing!

Jennifer

• ###### 7. Re: Check if record meets certain criteria then sum those records

Thank you both for your help. Joe's formula did work.

I'll be taking a course soon, but needed to wrap my head around some of this to get something out by EOW.

Best, Lisa