1 2 Previous Next 24 Replies Latest reply on Aug 26, 2016 10:11 AM by Fabio Garcia

# Calculation - Accumulated

Good morning, people!

I need to make a calculation that looked super simple, but I'm in trouble because of the date filter.

I'm showing the amount of leads per day, in the month of August. I need to include a second graph of the cumulative amount of leads from

a year ago, showing the same days of August.

Example: in the day bar on August 1, the calculation should consider the cumulative August 1, 2015 until August 1, 2016, the second bar (August 2) the cumulative August 2, 2015 until August 2, 2016 August and so on.

However, because of the filter, my date is limited to only August 2016 and the calculation does not work.

Tks!

• ###### 1. Re: Calculation - Accumulated

Fabio, you're using quick filters?

Best way doing it is using parameters.

Can you post a twbx file?

• ###### 2. Re: Calculation - Accumulated

Hi Fabio, a packaged workbook will be helpful, but I think you can get around this issue two ways: level-of-detail (LOD) calculations (if you're using Tableau 10) or table calculation filters.

LOD calcs built using the FIXED keyword work at the context level, which means that non-context filters (i.e. filters on the sheet that aren't gray) don't affect them at all. LOD calcs are available in Tableau 9, but I think that you'll need Tableau 10 for my workaround because you need to use an expression in the LOD dimensionality.

Table calculation filters, on the other hand, can filter what's visible to the user without changing the underlying data or level of detail. For instance, you could build a monthly view with a 12-month rolling sum of leads, and with table calculations, you can show only a subset of the months. Let's say that you wanted to show only the last month. Using a filter where "LAST() = 0" is TRUE would keep only the last month.

Let me know if either of these potential solutions might interest you.

• ###### 3. Re: Calculation - Accumulated

Hi David!

Thank you for your help. I'm using version 9.3, so I will try to follow the second alternative.

Are there more details for this explanation?

• ###### 4. Re: Calculation - Accumulated

Hi, Luciano!

How could I post a twbx file? I see only video and pic format to send.

Tks!!!

• ###### 5. Re: Calculation - Accumulated

Não sei se você enxerga isso, mas quando comento têm a opção usar editor avançado:

Quando vc clica nela, aparece a opção anexar.

• ###### 6. Re: Calculation - Accumulated

Hi Fabio, here are the steps I would take:

1. Add a filter that keeps exactly one year of data (to be used for your cumulative calculation). For instance, if you want it to end as of today, you can use the relative date filter and set it to 1 year. If you want the user to be able to set the end date, then we can discuss other options.
2. Put discrete months into columns as you've done in your screenshot.
3. Add [QTD Leads] as a measure to your view and use a Quick Table Calculation to get the total over the year. Running Sum should work fine. This will be your cumulative calculation.
5. Create a calculated field with this formula:

LAST() = 0

Set the default table calculation (in the bottom-right corner of the editor) to be along your date dimension.
6. Put this new calculated field into your filters and filter in only TRUE values. Edit the table calculation (right-click the filter) and make sure that it's computing along the date dimension.

Assuming that you configured the table calculation correctly, this will hide all but the last month, but it won't filter out the rest of the data.

• ###### 7. Re: Calculation - Accumulated

Pra mim não aparece essa opção.

Será que você foi por outro caminho?

• ###### 8. Re: Calculation - Accumulated

Não.

É que no seu caso, você criou a discussão.

Provavelmente você tria que editar a discussão e aí sim deve ter uma opção.

• ###### 9. Re: Calculation - Accumulated

Ah, tem essa opção quando eu abro uma discussão. Posso abrir em uma nova?

• ###### 10. Re: Calculation - Accumulated

Poder pode mas será que não dá pra editar essa?

• ###### 11. Re: Calculation - Accumulated

Onde eu consigo editar?

Fui na minha primeira mensagem mas só aparece as opções de Curtir, Responder e Compartilhar.

• ###### 12. Re: Calculation - Accumulated

Não é na mensagem:

Vai no seu profile:

Clica no título da discussão e depois editar:

Na edição aparece o anexar.

• ###### 13. Re: Calculation - Accumulated

Mandei o .twbx. Apareceu ai?

• ###### 14. Re: Calculation - Accumulated

Hi, David! First of all, thanks for your help.

I think we're almost there.

The cumulative leads in last year was right, but it is only displayed the month of August. I tried to detail the days of August (which is the goal), but only appears the last day of the month, the 25th in the case.

The idea was to show the accumulated amount of a year on August 1, August 2 and so on, until today (25th August), in 25 bars.

1 2 Previous Next