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

Using a filter within a Calculated Field

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

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

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

\

 dimention auto house Account age account 1 account 2 account 3 account 4 account 1 account 2 account 3 account 4 0 months 1 1 1month 1 1 4months 1 6months 1 7 months 1 1 auto house measure account 1 account 2 account 3 account 4 account 1 account 2 account 3 account 4 zero balance 1 1 1 1

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?