Forum Discussion

Rmilczarek's avatar
Rmilczarek
Helper I
8 years ago
Solved

Case Backlog Age

I had a previous post wherein I was looking for the average of cases open per month for a defined time period and the calculations seem to work well.  However, now I have further analysis I am trying...
Need Help
  • fhill's avatar
    fhill
    8 years ago

    Ok, I made some small changes to my code to account for Cases opening on Month End, but in general the AllSelected worked...  Remember to create a new Table with Month Start and Month End, and use this 'Months' table to create new Columns to calculate your timeframe values.

     

    ** New Column on your Case Table **

    Adj_CloseDate = IF (ISBLANK(Table1[EffectiveClosedDate__c]), TODAY(),IF(Table1[EffectiveClosedDate__c] < Table1[CreatedDate],Table1[CreatedDate],Table1[EffectiveClosedDate__c]))

     

     

    ** All these columns go on the Months (open & Close) table.

    <60 = CALCULATE(COUNT(Table1[CaseNumber]), FILTER(ALLSELECTED(Table1),
    Table1[CreatedDate] <= tbl_Months[EndOfMonth] && DATEDIFF(Table1[CreatedDate], IF(Table1[Adj_CloseDate] > tbl_Months[EndOfMonth], tbl_Months[EndOfMonth],Table1[Adj_CloseDate]),DAY) < 60 && Table1[Adj_CloseDate] >= tbl_Months[EndOfMonth]))

     

    60-120 = CALCULATE(COUNT(Table1[CaseNumber]), FILTER(ALLSELECTED(Table1),
    Table1[CreatedDate] <= tbl_Months[EndOfMonth] && DATEDIFF(Table1[CreatedDate], IF(Table1[Adj_CloseDate] > tbl_Months[EndOfMonth], tbl_Months[EndOfMonth],Table1[Adj_CloseDate]),DAY) >= 60 && DATEDIFF(Table1[CreatedDate], IF(Table1[Adj_CloseDate] > tbl_Months[EndOfMonth], tbl_Months[EndOfMonth] , Table1[Adj_CloseDate]),DAY) < 120 && Table1[Adj_CloseDate] >= tbl_Months[EndOfMonth]))

     

    120-180 = CALCULATE(COUNT(Table1[CaseNumber]), FILTER(ALLSELECTED(Table1),
    Table1[CreatedDate] <= tbl_Months[EndOfMonth] && DATEDIFF(Table1[CreatedDate], IF(Table1[Adj_CloseDate] > tbl_Months[EndOfMonth], tbl_Months[EndOfMonth],Table1[Adj_CloseDate]),DAY) >= 120 && DATEDIFF(Table1[CreatedDate], IF(Table1[Adj_CloseDate] > tbl_Months[EndOfMonth], tbl_Months[EndOfMonth] , Table1[Adj_CloseDate]),DAY) < 180 && Table1[Adj_CloseDate] >= tbl_Months[EndOfMonth]))

     

    >180 = CALCULATE(COUNT(Table1[CaseNumber]), FILTER(ALL(Table1),
    Table1[CreatedDate] <= tbl_Months[EndOfMonth] && DATEDIFF(Table1[CreatedDate], IF(Table1[Adj_CloseDate] > tbl_Months[EndOfMonth], tbl_Months[EndOfMonth],Table1[Adj_CloseDate]),DAY) >= 180 && Table1[Adj_CloseDate] >= tbl_Months[EndOfMonth]))

     

    ** for the purpsoe of the screen shot, >180 is set to >90 since this is all pretty recent data...

     

Rmilczarek's avatar
Rmilczarek
Helper I
8 years ago

I have made great strides on this (I think) thanks to your help.  I am facing a bit of a data bloat dilemma now though as the numbers I am getting on my chart are much higher than they should be.  I am pretty sure it is because when I use the data from the Months table that I created, it is using the entire dataset but in my other charts in this report I am using data from the Case table which is filtered to show only cases from certain teams.  

 

Can you suggest a way I can apply these Case table filters to the Months table I created?  I've tried a few ways via relationships (causes the chart to crash) or via column adds (either cannot get the data I want from the other table or it shows no data).

  

Here is what the chart looks like now so I am definately on the right track!

fhill's avatar
fhill
Resident Rockstar
8 years ago

Change every ... FILTER ( ALL( ... to   FILTER(ALLSELECTED(...

  • Rmilczarek's avatar
    Rmilczarek
    Helper I
    8 years ago

    Hmmm... Replacing ALL with ALLSELECTED does not seem to alter the data any.  It seems that any filters from the Case table that I add to the chart are still being ignored.  Is there some sort of relationship I need to establish between these tables for it to work properly?

     

    Sorry for all the questions, I am so close here I just have been looking at this for days now :)

     

     

    • fhill's avatar
      fhill
      Resident Rockstar
      8 years ago

      Try ALLEXCEPT. If that doesnt work, can you post a sample of yourdata with the additional filtered rows so i can duplicate on my end?

      • Rmilczarek's avatar
        Rmilczarek
        Helper I
        8 years ago

        Sadly ALLEXCEPT didn't seem to budge the data either.  I'll get a sample of the data together to attach but in the meantime, here is the method I am using to determine the Backlog for the months in question.  I have verified this matches the actual backlog, so I know this method is accurate.  Is there any way to add this type of formula/setup to "define" the parameters of a Backlog Age by Month report?  

         

        Here is what I used for Backlog:

         

        Backlog - Monthly = CALCULATE('Case'[Backlog],FILTER(ALL('Calendar'),'Calendar'[Date]<=MIN('Calendar'[Date])))

         

        Shared Axis is defined on the Calendar table I made as  Monthly = FORMAT('Calendar'[Date],"YYYY/mm")

         

         

        Again, I'll get some sample data together and thanks for all of your help!