Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi – I am looking for help with expressions to get an excel type “countifs” result.
Basically I have a table with cases data that is similar to the below:
Case Number | Case Status | Created On | Closed Date |
CP01 | Closed | 20/9/2013 | 14/01/2015 |
CP02 | Open | 21/11/2013 | |
CP03 | Closed | 22/12/2014 | 12/01/2015 |
CP04 | Closed | 23/12/2014 | 13/01/2015 |
CP05 | Open | 24/12/2014 | |
CP06 | Closed | 22/12/2014 | 13/01/2015 |
CP07 | Open | 26/12/2014 | |
CP08 | Open | 2/1/2015 |
|
CP09 | Open | 5/1/2015 |
|
CP10 | Open | 7/1/2015 |
|
The results I am after are similar to:
Canonical Year | Canonical Month | No. Cases Created | No. Cases Closed | % of cases closed within 60 days that closed that month |
2013 | 9 | 1 | 0 |
|
2014 | 12 | 5 | 0 |
|
2015 | 1 | 3 | 4 | 75% |
All fine with the No. cases created [Using: Count({$<DateType={'Created'}>} [CaseNumber]) ] and No. Cases Closed [Using: Count({$<[Case Status]={'Closed'},DateType={'Closed'}>}[CaseNumber])] , but can’t get the % of cases closed within 60 days (the KPI we are measuring)
I’ve tried the following and variations of this, but with no luck…
if(([ClosedDate]-[CreatedOn])<=60,
Count({$<[CaseStatus]={'Closed'},DateType={'Closed'}>}[CaseNumber])
)
My data model is similar to the below:
Try using this formula to get the # days between dates:
=
Above([Closed Date])-
[Created On]
Check out the attached. I mocked up a chart based on attached Excel file. Not sure if this is where you are going.