4 Replies Latest reply on Jun 22, 2018 4:03 PM by Deepak Rai

    Previous Month's Period

    Scott Schmeling

      I am trying to create a table that will look like this:

       

      April 2018
      May 2018
      Difference
      123456333

       

      However the current data I am working with is through June.  and this needs to be a dual axis table as I am trying to format the Difference Column only.  The issue I am running into is when I try to use the following formulas

       

      1. I created a formula to find the last date of teh data:

       

          Max([Sales Date])

       

      2. I created a formula to find the previous month:

        

           Dateadd('month', -1, [Sales Date])

       

      3. Now I am trying to do a comparison of the Sales DAte to the Previous Month:  This is where I get the error. 

       

           If DatePart('month',[sale Date] = DatePart('month',[Previous Month]) Then 1

          Else 0

          End

       

      The error I am receiving is the "Cannot mix Aggregate and NonAggregate in the same formula.  However if I put an Attr in front of part of it I always end up with a 0 never a 1.  This is just part of the formula that I intend to build. as I need to find other areas.

       

      Any ideas would be helpful and I'm sorry I cannot post a book as it has HIPAA information in it. 

       

      Thanks in advance.  If you can't help based on explanation I understand I"m having trouble describing exactly what I need. 

        • 1. Re: Previous Month's Period
          Deepak Rai

          Last date of Data

          DATETRUNC('month',{max(Sales Date)}) will return 6/1/2018

           

          Previous Month

          Dateadd('month',-1,Last date of data) will return 5/1/2018

           

          Now if this ok then what is next? we can proceed step by step

          Thanks

          Deepak

          • 2. Re: Previous Month's Period
            Scott Schmeling

            Thanks Deepak,

             

            I now need to count based on the following:

             

            I am trying to find the number of items shipped within 2 week s of sales date.  By month, while only showing the previous 2 months. 

             

            So I extrapolated what you sent to create a month prior to previous month. 

             

            Now I need to calculate for each of those months individually the count of client IDs that was shipped.  Again, I need to do this as a dual axis so I can custom format individual columns.  That's what is making it so tricky.  Plus there are other columns at the end hence I can't use basic Table Calcs.

             

            Here's teh two weeks formula- it is a little more involved than I explained above.  There is no data so I can share this.

             

            if ([Retinopathy Level]='Severe Diabetic Retinopathy (Will Not Repeat Photos)')

            and(datediff('week',[Exam Interpretation Date],[Op Appt Date]) <=2) then 1

            else 0

            end

             

            I'm trying to combine the formulas into one so I can jsut have a column calculated individually for each month.  AS well I am going to create a filter using the Previous Month and Previous Month -1 to keep it updated.

            Thanks,

            • 3. Re: Previous Month's Period
              Scott Schmeling

              Figured it out-

               

              If Previous = 1 and SEvere = 1 Then 1

              Else 0

              End.

               

              Works like a charm.

               

              Thanks Deepak for getting me in the ball park with the solution.