5 Replies Latest reply on Jul 30, 2018 4:54 AM by Simon Runc

    Display Data if date is less than Certain Date

    AJ Singh

      Hello,

       

      I am trying to create a filter to limit the future unfinished data. So, I need your help with calculation: So logic would be:

       

      If Today() < 6/1/2018 than display Data for Program Date < 6/1/2017 which sounds easy but when Today () hit 6/1/2018 then cycle restart and now need to compare the 6/1/2019 and display the data for program Data <6/1/2017 so on.

       

      Please help

       

      AJ

        • 1. Re: Display Data if date is less than Certain Date
          Jim Dehner

          Hi

           

          2 part answer

          1 the end date is

           

          Then you use the "End Date" in your calculation similar to

           

           

          Jim

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          2 of 2 people found this helpful
          • 2. Re: Display Data if date is less than Certain Date
            AJ Singh

            Thanks a lot! this worked for me

            • 3. Re: Display Data if date is less than Certain Date
              Jim Dehner

              Glad to help out

              Please mark the reply correct to close the thread

              Thanks

              Jim

              1 of 1 people found this helpful
              • 4. Re: Display Data if date is less than Certain Date
                Satish Pandey

                My question is almost similar and tried the same logic, however, getting an error.

                 

                Here is my requirement.

                I need to calculate Phase 1 based on Filing and Received date.

                 

                Phase1 = Invoices received within 6 months of filing. I have received date and invoice amount fields.

                 

                For this logic, i created a first formula

                 

                "FilingDate+6Months" = filing date+ 180 days

                 

                Now I am trying to create another formula

                 

                If ATTR([Received Date]) <= [FilingDate+6Months] then sum([InvoiceAmountt]) ELSE '0' END

                 

                For this, i am getting an error "Can not mix aggregate and non-aggregate argument with this function".

                 

                I am not sure if this is the right way to display data based on two different dates.

                 

                Simon Runc  Barry Plum

                • 5. Re: Display Data if date is less than Certain Date
                  Simon Runc

                  hi Satish,

                   

                  So there is a couple of issues with the formula...

                   

                  If ATTR([Received Date]) <= [FilingDate+6Months] then sum([InvoiceAmountt]) ELSE '0' END

                   

                  ATTR([Received Date]) is an aggregate calculation (as it's wrapped in the ATTR aggregation) but [FilingDate+6Months] isn't an aggregate

                   

                  What will work best is dependent on your VizLoD, as aggregate calculations depend on the VizLoD, but assuming the VizLoD is such that ATTR([Received Date]) is OK, the you can also wrap the  [FilingDate+6Months] in an ATTR to make it an aggregate too.

                   

                  The other problem, is that the ELSE return is a string (as the zero is wrapped in ''), whereas (I assume) SUM(InvoiceAmount) is a number, so you need to remove the quotes around the zero (we can't have 2 different return data types, depending on the condition.

                   

                  Hope that makes sense.