Tableau Fill missing value with latest value available

Question

I just started to use Tableau and I would like to know how to take the latest value available. For example I have :

ID Date          Active
1  01/01/2016       1
1  01/02/2016       1
1  01/07/2016       0
2  01/02/2016       1
2  01/08/2016       0

Now I would like to have a view by month on the SUM of the Active flag, something like :

01/01/2016       1
01/02/2016       2
01/03/2016       2
01/04/2016       2
01/04/2016       2
01/05/2016       2
01/06/2016       2
01/07/2016       1
01/08/2016       0

As you can see we assume that the Active flag take the latest value available like :

1  01/01/2016       1
1  01/02/2016       1
1  01/07/2016       0

Will be transform in :

01/01/2016       1
01/02/2016       1
01/03/2016       1
01/04/2016       1
01/04/2016       1
01/05/2016       1
01/06/2016       1
01/07/2016       0

After you do the sum on the Active flag.

I think that I have to use Calculated field but I didn't manage t find the right formula.

spijs spijs · Accepted Answer · 2017-02-08T08:08:12

I assume your example contains an error so I give you the solution for the problem as I understand it. Please explain how I misinterpreted. I do feel like the techniques used should apply nevertheless.

I think you want two things: first you want Tableau to show missing months, which you can do by right clicking on the months and selecting show missing values. This would give you:

Month of Date       Active
January             1
February            2
March       
April    
May   
June       
July                0  
August              0

Secondly you would like missing values to have the value of the result of the previous month.

Month of Date       Active
January             1
February            2
March               2
April               2
May                 2
June                2
July                0  
August              0

And here I have a difference from your example since you state July should have a value of 1 which I don't understand since the sum of July is 0. If it is the case that this is just due to a typo you can achieve the above table by indeed using a calculated field:

ifnull(sum([Active]), previous_value(0))

If I misinterpreted some part of your problem, please let me know so I change my solution accordingly. But I think in general a combination of lookup, ifnull and previous_value will be able to solve your issue.

Tableau Fill missing value with latest value available

1 Answers