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


      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



      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



          • 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



            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.


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

              Figured it out-


              If Previous = 1 and SEvere = 1 Then 1

              Else 0



              Works like a charm.


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