1 Reply Latest reply: Oct 15, 2018 2:57 PM by Mark Ritter RSS

    Help Converting IF To Set Analysis

    Craig Brett

      Hi There

       

      Could someone help me convert the below if statement into set analysis. I have been trying all day without success.

       

      The below formula counts help desk ticket ID's that we created in the previous month. "Tickets Brought Forward From Previous Months" Would be the title of the chart. [Master MonthYear] is the charts dimension which belongs to a fully disconnected calendar table. It is slow but it's the only way I can get it to work, hoping set analysis will maybe help it run faster.

       

      Count(DISTINCT(if([Created MonthYear] = Date(AddMonths([Master MonthYear],-1),'MMM-YYYY') And [User Department] = 'IT' AND ([Closed MonthYear] >= [Master MonthYear] or IsNull([Closed MonthYear])),[Ticket ID])))

       

      Thanks!

        • Re: Help Converting IF To Set Analysis
          Mark Ritter

          Count( {<Department={'IT'} , [Created Month Year] = {'=Date(AddMonths([Master Month Year],-1)'},

                      [Closed Month Year] = {'>=[Master Month Year]'}  >}   [Ticket ID])

           

          Hard to do this without data and ability to test.  Not sure this is 100% correct.  But should give you the idea.