1 2 Previous Next 16 Replies Latest reply on May 5, 2018 5:09 PM by Don Wise

# Running Sum but Exclude Date Range quick filter

Hello Tableau community!

I have a request and having issues coming up with a solution.

For example I have create date, user name and the total number of records:

I need to use "create date" as a quick filter and display like below.

The works as expected but not what is needed.

What I need is to show the total count of records for the full time period even if the start date in the create date is filtered. So instead of showing the 8 records I need to see 30. The only filtering should be when the end date of the create date is filtered.

I've attached a sample workbook. I've tried some LOD calculations and start date / end date parameters but not sure the best approach.

Thanks

• ###### 1. Re: Running Sum but Exclude Date Range quick filter

Hi Matthew.

See below screenshot and newly attached workbook.  Please mark this as correct if it answers your question and/or helpful if it pointed you in the right direction, so that others may find it in the future.

1 of 1 people found this helpful
• ###### 2. Re: Running Sum but Exclude Date Range quick filter

Don Wise thanks for the response! I need the records to filter if the end date range in the quick filter is moved to the left. Otherwise this is working as expected. Any ideas?

• ###### 3. Re: Running Sum but Exclude Date Range quick filter

Try adding the Filter to CONTEXT

• ###### 4. Re: Running Sum but Exclude Date Range quick filter

Context filter doesn't give the desired results.

• ###### 5. Re: Running Sum but Exclude Date Range quick filter

Deepak,

I think he's now looking for something of a running sum so that when he moves the end date filter from the right to the left, the calc reduces the Total Domain number, separate from the date filter count.  So, there might be a total number of 30 records in the domain, but, in the view for the date range there are 8; however, in moving the date filter backwards, the number 30 should reduce downwards in addition to the number within the date range.  Matthew, correct me if I'm wrong on that assumption.

LOD's can't be used on an aggregate like a running sum, so maybe Deepak has some ideas.

Thx, Don

• ###### 6. Re: Running Sum but Exclude Date Range quick filter

I need to understand this

• ###### 7. Re: Running Sum but Exclude Date Range quick filter

HI Matt,

You probably means this?

Filtering the View Without Filtering Underlying Data | Tableau Software

Thanks,

Shin

• ###### 8. Re: Running Sum but Exclude Date Range quick filter

Don, you're correct on your assumptions. That is making this tricky.

• ###### 9. Re: Running Sum but Exclude Date Range quick filter

Hi Matthew,

I was able to get one worksheet to display contextually what you're looking for by using Index() function on one column (called Number of Users in View) and a separate Table Calc (grand totals do not work with Table Calc's BTW) called Number of Users in Table, using the following calc:  WINDOW_MAX(ATTR(DATENAME('day',[Create date]))).  This will give you a detailed view of the table as to how many are in the view vs. how many total in the table.  HOWEVER, this doesn't work for your summary worksheet Total Users Filtered.

So, I'm hoping that either Deepak Rai or Shinichiro Murakami may now have a better idea as to what you're trying to achieve.

I've also copied/pasted my prior response to you (for them) so they each have the additional context: "he's now looking for something of a running sum so that when he moves the end date filter from the right to the left, the calc reduces the Total Domain number, separate from the date filter count.  So, there might be a total number of 30 records in the domain, but, in the view for the date range there are 8; however, in moving the date filter backwards, the number 30 should reduce downwards in addition to the number within the date range."

• ###### 10. Re: Running Sum but Exclude Date Range quick filter

Thanks again Don for looking into this!

Unfortunately I need it to work in the summary view. Deepak Rai or Shinichiro Murakami if you guys have any other ideas or solutions I'd appreciate it! If I figure out a solution I'll post it here too. Thanks again guys!

• ###### 11. Re: Running Sum but Exclude Date Range quick filter

Not exactly sure, but like this?

Start filter limit the view only (still count from 4/1)

End filter limits the view and count (only count up to end date)

Thanks,

Shin

• ###### 12. Re: Running Sum but Exclude Date Range quick filter

Thanks Shin!

I need it in a single record view though. For example if I use an LOD calculation I can do {FIXED : COUNT(Number of Records)}. Then when I filter the create date (left to right) it still shows me the running sum which is 30 which is what I want:

The issue is I need to be able to filter the create date (right to left) and show a running sum of records up to this point.  Since I'm using the LOD above I'm just seeing the total number of records and its not filtering.

Thanks

• ###### 13. Re: Running Sum but Exclude Date Range quick filter

I don't have idea with one filter option.

I think it theoretically conflicting.

I tried my best already to balance function and UI.

No more idea so far.

Shin

• ###### 14. Re: Running Sum but Exclude Date Range quick filter

Hi Matthew,

See if the newly attached works for you?  The summary sheet involves a standard calc with an LOD calc.

It appears to meet your requirement.  However, thinking this through, this will only work if there's one date for one user for a true 1:1 relationship.  Otherwise, I can see this not working out for you if there's more data on one side than the other.  For example, multiple users within a single date...these calc's involve just your dates, not your User column.