Running total does not work when I hide months for a vlaue that does not exist in the month

tableaucommunity@tableau.com — Tue, 29 Apr 2014 14:01:36 GMT

Hi,

Firstly, sorry about the confusing heading.

Essentially what I am doing is filtering for the current month (hiding really, using "LOOKUP(MIN([Date]),0)") trying to return the top 20 Sales folk based on the running total Sales for the year for that person.

So for example:

Salesman A had sales of $1000 in Jan, $1500 in Feb, $500 in Mar and nothing in Apr (there is absolutely no data for Salesman A in April); giving a year-to-date total of $3000.

Salesman B had sales of $300 in Jan, $500 in Feb, $200 in Mar and $500 in Apr; giving a year-to-date total of $1500.

When I filter (hide) to only see April I only get running total data for Salesman B, even though the ranking still works; i.e. Salesman B is ranked 2nd.

If I remove the filter (hide) on April, I get the running total for Salesman A of $3000 in April month but as soon as I put on the filter on it gets dropped.

I've tried using the IFNULL function on the Sales field but no luck.

The formula I tried was:

IFNULL(LOOKUP(RUNNING_SUM(SUM([Cal Year Sales])),0), LOOKUP(RUNNING_SUM(SUM([Cal Year Sales])),-1)

Any ideas on how to hide months but still return the running total even if there is no data for the month your filtering on?

Content in Tableau Community Forums

Running total does not work when I hide months for a vlaue that does not exist in the month