3 Replies Latest reply on May 23, 2016 4:34 PM by Tom W

    Date Calculations Help!!

    anushachowdary.sakhamuri

      Hi,

      I want my view to look like this:

      Label   cp>1(7 days)  cp>1(30days)   cp (today)

      x           25%                 50%                       37%

      y           50%                90%                        90%

      z           46%                82%                         44%

      a          80%                 73%                         46%

      b          23%                 37%                         37%

      fg         12%                  44%                         44%

       

      Here is how i calculate cp, which an LOD calculation:

      cp = {INCLUDE  [Label],[feat] : (MAX(upper tol) - MAX(lower tol))/(6*STDEV([DIP])+0.0001)}

       

      Now to get % of feat's with cp>1, I use these two calculations :

      1) cp in/out =   IF [cp] > 1 THEN 1 ELSE 0 END

      2) cp> 1 =     sum(cp>1)/countd(feat)

       

      Now I want to write a calculated field to get cp for last 7 days and 30 days

       

      7 days =  (IF TODAY() - ATTR(Date) <= 7 THEN [cp> 1] ELSE 0 END)

      30 days = 

      today =

       

      These calculations some how don't seem to work ..they are returning zeros and if I do a sum

      SUM (IF TODAY() - ATTR(Date) <= 7 THEN [cp> 1] ELSE 0 END)

       

      I am getting an error saying its already an aggregate and cannot be further aggregated.

       

      My question is very similar to this post.

      Re: How do i display Last 7 days and Last 30 days Sales ?

      But it is not working for me.

       

      Thanks in advance!!

        • 1. Re: Date Calculations Help!!
          Tom W

          Hi there,

          Thanks for the details. It's difficult to help without a packaged workbook though. Can you please attach a Packaged Workbook version to support your scenario?

          • 2. Re: Date Calculations Help!!
            anushachowdary.sakhamuri

            Hi Tom,

             

            Thanks for the reply . Unfortunately I cant publish my workbook.

            But I have another question .

             

            IF DATEDIFF('day', DATE([S_Date]), TODAY()) <= 7  THEN "Last 7 Days" 

            ELSEIF DATEDIFF('day', DATE([S_Date]), TODAY()) <= 30 THEN "30 Days" 

            END

            the 7 days is giving me the right results but 30 days gives me the days minus the seven days.

            Not sure how to get the last 30 days.

             

            Thank you

            • 3. Re: Date Calculations Help!!
              Tom W

              anushachowdary.sakhamuri wrote:

               

               

              the 7 days is giving me the right results but 30 days gives me the days minus the seven days.

              Not sure how to get the last 30 days.

               

              That doesn't make sense - your calculation isn't returning the result of the datediff so I'm not sure how you're arriving at this conclusion. Without seeing an example though, I have no idea how you're arriving at any conclusion.

              You don't need to use your actual data, you can mockup an example in Excel. It's really impossible to help you otherwise as I can replicate your formula working exactly as I'd expect - see attached.