Extract Last Value as Metric from Table Calculation in Tableau

0 votes

In Tableau, I have the following raw data:

Month,Total
2021-08,17
2021-09,34
2021-10,41
2021-11,26
2021-12,6

Using the formula below, you may arrive to the following result.

RUNNING_SUM( 
COUNTD(IF [Inserted At]>=[Parameters].[Start Date]
       AND [Inserted At]<=[End Date]
THEN [Id] ELSE NULL END
))

/

LOOKUP(RUNNING_SUM( 
COUNTD(IF [Inserted At]>=[Parameters].[Start Date]
       AND [Inserted At]<=[End Date]
THEN [Id] ELSE NULL END
)),-1)*100-100

We get,

Month,My_Calc
2021-08,NULL
2021-09,200
2021-10,80.4
2021-11,28.3
2021-12,5.1

And all I actually want as a major measure is 5.1 (last monthly value) ( percent Month-Over-Month Growth).

I'm not sure how I'm going to do it.

I'm new to Tableau and have no idea how to utilise calculated fields in combination with the date groups feature to determine month-over-month growth. I tried using the native year-over-year growth running total table calculation, but it didn't work since my calculating technique is different.

Apr 11, 2022 in Tableau by Vaani
• 7,070 points
2,019 views

1 answer to this question.

0 votes

First, a quick introduction to table calculations, followed by the answer at the end.

The data source (e.g. database server) does the majority of Tableau's calculations, and the results are subsequently delivered to Tableau (i.e. the client) for presentation. Even when dealing with very huge data sets, this division of duties allows for good performance.

Table computations, on the other hand, work with the table of query results received from the server. They are carried out near the end of the pipeline's order of operations. That's why table calcs work with aggregated data, thus you'll need to ask for WINDOW SUM(SUM([Sales)) rather than WINDOW SUM([Sales)) ([Sales])

Before presenting the query results obtained from the data source to the user, you may utilise table calcs to do last passes of computations over them. You can do things like compute a running total or dynamically change the visualisation layout based on the query results. The computation is only one component of defining a table calc, therefore this freedom comes at a cost. You must also define how the calculation should be applied to the table of summary results, a process called as partitioning and addressing. A useful definition of partitioning and addressing may be found in the Tableau online documentation.

Table calculations, also known as vectors or windows, are applied to blocks of summary data at a time. Partitioning is how you tell Tableau how you want the summary query results broken up into panes so you can apply your table calc. The way you indicate the sequence in which you want to traverse those partitions is using addressing. Some table calcs, such as RUNNING SUM, require addressing, whereas others, such as WINDOW SUM, do not.

To truly comprehend table calculations, it is also beneficial to learn about the functions INDEX(), SIZE(), FIRST(), LAST(), WINDOW SUM(), LOOKUP(), and (eventually) PREVIOUS VALUE(). You'll be able to implement all of these functions utilising only two of them as the essential ones if you properly grasp them.

Finally, to answer your question in part:

To determine if you are at the last value of your division, use the boolean expression LAST() = 0. You may hide all the other values by using that formula as a filter. You'll need to make sure the partitioning and addressing are set up appropriately. You'd be retrieving a batch of data from your server, using it in client calculations, but just displaying a portion of it. Depending on which fields are on which shelves, this can be a bit brittle, but it can work.

If you can avoid requesting data exclusively for client-side calculations, it's usually more efficient to utilise a calculation that can be performed server-side, such as LOD calc. Table calcs, on the other hand, are a helpful tool if the data has already been fetched for another purpose or if the calculation requires table calc features, such as the ability to depend on the order of the values.

Unlock the Power of Data Visualization with Our Tableau Course.

answered Apr 12, 2022 by Neha
• 9,020 points

Related Questions In Tableau

0 votes
1 answer

In Tableau, How to sum the last 7 days of the value?

It can be achieved easily by creating ...READ MORE

answered Mar 12, 2019 in Tableau by Cherukuri
• 33,030 points
17,215 views
0 votes
1 answer

Is it possible to pass a Measure Value in Tableau as a parameter?

Hi, Yes, it is possible to pass a ...READ MORE

answered Mar 14, 2019 in Tableau by Cherukuri
• 33,030 points
1,864 views
0 votes
1 answer

How to color code cells of a column based on the text value in Tableau

You can use the following steps to ...READ MORE

answered Mar 27, 2018 in Tableau by Atul
• 10,240 points
9,952 views
0 votes
1 answer

MDX query in Tableau pivot table

You can view them here - \My Documents\My ...READ MORE

answered Apr 6, 2018 in Tableau by QueenBee
• 1,820 points
871 views
+1 vote
1 answer

Is it possible to access or install tableau desktop insde tableau server?

Try to understand this, Tableau Server and ...READ MORE

answered Mar 30, 2018 in Tableau by xyz
• 1,560 points

edited Mar 30, 2018 by xyz 872 views
0 votes
1 answer

Hover text shows numbers in decimal points

Do this: 1. Right click on the calculated ...READ MORE

answered Apr 3, 2018 in Tableau by QueenBee
• 1,820 points
1,000 views
0 votes
1 answer

Dropdown to change x-axis in Tableau

You need to create a  str datatype parameter ...READ MORE

answered Apr 4, 2018 in Tableau by QueenBee
• 1,820 points
1,321 views
0 votes
1 answer

Tableau Desktop- Tableau Server

No, you don't necessarily need to purchase ...READ MORE

answered Apr 4, 2018 in Tableau by QueenBee
• 1,820 points
753 views
0 votes
1 answer

How to aggregate table calculation in tableau

You may proceed with Max and Min ...READ MORE

answered Apr 7, 2022 in Tableau by Neha
• 9,020 points
1,401 views
0 votes
1 answer

How to fix spelling error in a field or column value using Tableau?

You should be able to use a ...READ MORE

answered Mar 10, 2022 in Tableau by Neha
• 9,020 points
781 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP