5 Replies Latest reply on Jan 16, 2017 7:27 AM by Yuriy Fal

# Cumulative table calculation_calculation of all items by using filter

Hi guys,

I would like to show on the dashboard  # Persons and # Calls per week. The number of persons and calls is cumulative. My problem is, when I set a filter for last 10 weeks, tableau beginns to count items from week I applied the filter und ignore all previuos items.

But I don't want to loose the calls from previous weeks. So I created some parameters and calculation field. (See below)

IN first step I create two parameters:

- Min date (01.01.2016)

- Max date (31.12.2016)

Second step: creating a calculation field: date cumulative

LOOKUP(MIN(DATETRUNC("week", [Activity Date])),0)>[Min Date] and LOOKUP(MIN(DATETRUNC("week", [Activity Date])),0)<[max date]

-- This formula gives me a choice between true or false.

It is important, because I would like to use the date filter for both sheets.

Third step: Creating a dashboard

- set "date cumulative" = true

use "activity date" as a relative filter

The problem is, that tableau still count items from the week I filter for. Does anybody have any ideas what is wrong with my formula?

Is it a formula or maybe smth else?

Thank you in advance!

Kind regards

• ###### 1. Re: Cumulative table calculation_calculation of all items by using filter

Not sure, if i got your question.

Do you want to show the weeks cumulative # Persons and # Calls. - Then remove the Date range filter..

If you are applying date range filter , then it will show only weeks coming in that date range.

• ###### 2. Re: Cumulative table calculation_calculation of all items by using filter

HI Shipral,

thank you for you response.

I would like to show # Persons and # Calls. Like on this screenshot:

The problem is, if I apply the date range filter, tableau shows me only the items beginning from the week I applied in the filter. Tableau doesn't counts items before.

Example:

I'm filtering on the period: Nov and Dec in 2016. Tableau ignores all items I had gather before (Jan - Oct) and beginns to count from Nov. I would like that tableau counts all items I gathered before Nov.

Do you have any ideas what was my mistake?

Thank you!

• ###### 3. Re: Cumulative table calculation_calculation of all items by using filter

Hi Dilyana,

Tableau ignores all earlier months, because date range filter is applied and it will only show values occurring within date range.

If you dont use date range filter, then it will show all the earlier values as well.

• ###### 4. Re: Cumulative table calculation_calculation of all items by using filter

Hi Shipral,

I know that. Thank you!

But I also know that it is wark to count all items. I have a mistake in my formula. The formula should be adjusted. Do you have any ideas?

Kind regards

• ###### 5. Re: Cumulative table calculation_calculation of all items by using filter

Hi Dilyana,

What you're trying to get is a little bit complicated,

because of mixing the two things:

1) To show RUNNING calculation results "filtered" by [Activity Date],

you're rightfully using the Table Calculation Filter based on LOOKUP().

The fine point here is Computing this filter calc along Cell.

When filtering on both sheets you have to use Parameters (rightfully so).

Any other "regular" filter by [Activity Date] would break the RUNNING Table Calc.

2) To calculate RUNNING Counts of DISTINCT [IDs] along [Activity Date]

you would like to count only the first occurrence of each [IDs] value along Date

using FIXED LOD expression, and then the result would be (running) summed up nicely.

Please find the attached with modifications (all on Dashboard 7).

Hope it could he of help.

Yours,

Yuri