4 Replies Latest reply on Feb 5, 2019 1:54 PM by Jim Dehner

    Aggregate string

    Kevin Smith

      When I'm pulling information for my database, I am comparing two separate date fields, and returning the max value has my "paid in full date" . Which is an aggregate.

       

      white issue comes when comparing that aggregate value in a calculated field, I am returning various string values such as, "withing review period" or "out of review period"

       

      Tableau however records those as aggregate values. Due to the original aggregated date. I need those values to be returned as strings, not an aggregatvalue that happens to be a string.

       

      Is there any way to do that?

        • 1. Re: Aggregate string
          Peter Fakan

          Hi Kevin,

           

          Uploading  sample .twbx would be helpful to be able to look at your problem.

           

          Have you tried STR(ATTR([your measure]) yet ?

          • 2. Re: Aggregate string
            Jim Dehner

            Hi Kevin

            see below

            If you do something like this to get your max  - you will get an aggregation

            but - this may be a bit of a hack -

            one of the cool properties of lods is that what they return is not aggregated

            so this will return the same values bu dis aggregated

             

             

             

             

            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.

            • 3. Re: Aggregate string
              Kevin Smith

              Unfortunately I can't upload it for to the proprietary information from work.

               

              I have, I just got an error saying it's already an aggregated function and it can't be done.

               

              Basically I have two custom sql that pill in max dates.

              I'm comparing those dates as date1 and date2 respectively.

              I then take those dates in a calculated field to get my max date of those fields.

               

              Max(date1,date2) as Paid_in_full

               

              Then have a calculated field "finding" that reads

              If Paid_in_Full >= today ()-7 Then "within audit"

              Else "Not with audit"

              End

               

              Because of Paid_in_Full being an aggregated field, finding is also returning as aggregated value and not as a string like I need it to.

               

              I need that to be a string, so I can compare it in a later calculation with other string results.

              • 4. Re: Aggregate string
                Jim Dehner

                see my response above

                 

                you could try make the paid in full an lod something like     {include [order]: max([date1]:max(date2] )   }

                and use that in the subsequent calculation

                Jim