2 Replies Latest reply on Oct 7, 2013 9:45 AM by Ania Kludka

    Using a filter within a Calculated Field

    Ania Kludka

      Hi,

      I'm new to the forum and I would appreciate your help. I have an issue I've been struggling with.

      I have a measure field that I'd like to include in my wokbook. Its called Zero Balance. It gives me number of accounts I have with negative balance (not the balance).

      I have different types of accounts (lets say auto accounts and house accounts).

      On top of that I would like to include a filter that will let me choose accounts with specific age. This is a Dimention field called AGE OF ACCOUNT.

      What I want to do is I want Tableau to give me automaticly ony those accounts with age higher then lets say 7 months if I choose house accounts and I want less then 7 months if I choose auto accounts. I dont want to do that manually every time I switch between the type of account.

       

       

      The perfect option is to create a duplicate of the measure Zero Balance and change somehow the duplication that will include the age inside the calculation.

      And then I could create another calculation field for example

      IF account = "AUTO" then ZERO BALANCE1

      ELSE ZERO BALANCE2

      END

       

       

      How can I do that?

        • 1. Re: Using a filter within a Calculated Field
          Shawn Wallwork

          Anna welcome to the forums! You question is a bit confusing. The problem sounds completely doable, but I am unclear how your data is laid out or structured. But you might be able to use some of these formulas to get to a solution:

           

          DATEADD('month', -7, TODAY())  // this will give you the date exactly 7 months ago (3/7/2013)

           

          If you have a date field in your data (you didn't mention one, unless [Age of Account] is a date type) you could use this in a calculation to separate your data like this:

           

          IF [MyDate] < DATEADD('month', -7, TODAY()) THEN [Zero_Balance]

          ELSE  [Zero_Balance]

          END

           

          But if there really is a data field named [Account] then you can get the results you're looking for using this:

           

          IF [Account]='Auto' THEN [Zero_Balance]

          ELSE [Zero_Balance]

          END

           

          You don't have to duplicate your field into 1 & 2, because IF statements are either/or.

           

          Hope this helps. If it doesn't, then help us help you by explaining what fields are available for calculations, what data type they are (date, number, string) and what sorts of values are stored in those fields in what format. Thanks,

           

          --Shawn

          • 2. Re: Re: Using a filter within a Calculated Field
            Ania Kludka

            Thanks Shawn for the help. Actually the age is not a date field. Let me drawa simple data base model and see if now you could help me

            \

             

            dimentionautohouse
            Account ageaccount 1account 2account 3account 4account 1account 2account 3account 4
            0 months11
            1month11
            4months1
            6months1
            7 months11
            autohouse
            measureaccount 1account 2account 3account 4account 1account 2account 3account 4
            zero balance1111

             

            As you can the data model is made of tags and gives 1 for those that meet the conditon.
            So now I'd like to put the Zero Balance in my sheet (will give me number 4 without any filter) and I want to be able to switch betwen auto and house and I want tableau to give me automaticaly different account age (for auto only the sum of those tha are less then 4 month so i want  taleau to give me the number 3) and house (only those less then 7 months so I want tableau to give me number 1 cos thee's only 1 account with higher age then 7 months).

             

            Does it help?