Limiting row displays without impacting underlying calculation

[Cassandra Kuang]

Hi,

I am trying to use Spotfire to monitor our process. We currently have data for 30 lots. I only want to show data for the last 6 lots on a bar chart with reference lines showing the average and average+/-SD of all 30 lots.  I am also trellising the columns so relevant columns/parameters can be displayed on one page. I tried using data limiting and show/hide items, but it would change the results on the average and other calculations to the average of the last 6 lots while it should be the average of all 30 lots.

Thanks,

Cassanadra 

 

[Olivier Keugue Tadaa]

Hi Cassandra,

One option is to use calculated columns

Let's consider this source table

if I plot a bar chat with avg and +/- stdv then I'll obtain this chart

Like you said, if you filter it will also impact the lines and their values

My proposal consists of first creating these columns

• avg ::: Avg([Value]) OVER (All([Lot]))
• stdev ::: StdDev([Value]) OVER(All([Lot]))
• avg-stdev :::: [avg]-[stdev]
• avg+stdev :::: [avg]+[stdev]

then add the three lines like this 

 

you will keep your lines' values if you filter

PS:
By the way, you can also just use the x-Axis zoom slider if that works for you.

 

[Olivier Keugue Tadaa]

Hi Cassandra,

Here is another easier solution.

If you tick the "Update Manually" box, you should be able to keep the value when filtering.

 

PS: Thanks @Gaia Paolini for the trick.

[Cassandra Kuang]

Hi Oliviver and Gaia,

Thanks for providing a solution. However, I have a total of ~ 60 parameters that I need to track and compare against the living average values (average of all lots). It is not that efficient to make so many calculated columns. Is there a better way to handle this more efficiently?

Thanks,

Cassandra

[Gaia Paolini]

the second suggestion does not need calculated columns, just clicking on Update Manually on the line.

[Cassandra Kuang]

Hi Gaia,

This is a living data table with more new data available as production continues. If I choose to update manually, it means I need to calculate the avg of all 60 parameters manually and update the value manually, correct? If so, are there any alternatives?

Thanks,

Cassandra

[Gaia Paolini]

There was a misunderstanding. 

Ticking "Update Manually"  means that in order to update it, you would have to click on the Update button, which you would not click. So it stays the same when you filter.

[Cassandra Kuang]

Just want to make sure that we are on the same page.

Consider the sample plot that Olivier created (thanks for creating that, Olivier). The average of 30 lots is 0.57.  Since it is a living data table, I will have data from lot 31, lot 32, lot 33, lot 34, and more to come. If I don't click the update button, the average stays the same, which is the average of first 30 lots. My goal is to have the chart to show only data from the latest 6 lots: lot 29, 30,31,32,33 34, and the average will be updated to the average of all 34 lots. 

 

Thanks for your help in advance.

[Olivier Keugue Tadaa]

Can you share a sample dataset that includes those parameters? There might be a possibility of either using scripts or transformations (pivot, unpivot etc)

[Cassandra Kuang]

Hi Olivier,

 

Here is a sample table with 10 of the many parameters. It has data for 20 lots.

Thanks in advance.

Sample Table for Autofilter and Auto-Calculation_WK17AUG2024.xlsx

[Cassandra Kuang]

To add more details, here is my current setup with data from the sample table. Since there are data from 17 lots, the lot number is getting cut off (x-axis). To resolve this, we decided only to show data/the bars of the last 6 lots but the average of all 17 lots. This is very helpful for us as we need to monitor process performance and we can do that by comparing data of the latest lot to the historical average.  Help this help make the goal clear.

[Olivier Keugue Tadaa]

Hi Cassandra

Thanks for the information. If your data size allows, I'd suggest changing your data table structure to a tall-skinny by unpivoting your data like this

and calculate the new columns like this 

• avg ::: Avg([Value]) over (Intersect(all([Lot#]),[Parameter]))
• stdev ::: Stdev([Value]) over (Intersect(all([Lot#]),[Parameter]))
• avg-stdev :::: [avg]-[stdev]
• avg+stdev :::: [avg]+[stdev]

You can then add the lines from the data table and choose the suitable calculated values.

I am attaching my dxp example.

PS: a tall skinny table will also make things easier for the visualizations' setup.

Sample Table for Autofilter and Auto-Calculation_WK17AUG2024 - Sheet1.dxp

[Cassandra Kuang]

Hi Olivier, 

I cannot open your dxp sample since my version is 12. Do you mind sending me another one that is compatible with my version?

Thanks,

Cassandra

[Olivier Keugue Tadaa]

no problem. I have created one with an older Spotfire version

 

Sample Table for Autofilter and Auto-Calculation_v10.dxp

[Vincent Thuilot]

Hello,

Could this be a solution?

 

Here what i did: 

1- Calculated the Lot number and formatted it as Integer (i assume this logic would need to evolve if the 2 characters change)
2- Unpivoted as suggested by Olivier
3- Calculated a new column to isolate the value corresponding of the last 6 lots

case WHEN [ID]>(UniqueCount([ID]) - 7) then [Value] END 

Again, you will surely have to adapt it based on your lot coding logic

4- Create a Combination Chart with a) the Value of the newly calculated column and b) an Average line with all Values and of course

here the avg formula that i used: 

Avg([Value]) OVER (All([Axis.X]))

5- Filter based on the new column, i put greater than 0 for the example but you could set it to Not Null or something else that better matches your needs.

 

6- Add the Treillis, adapt Y axis scale for each treillis panel and voila :)

Not sure it will satisfy your needs but maybe it will help in your thinking.

Vincent

Combination Chart Try.dxp

[Olivier Keugue Tadaa]

This is also a good approach since they will not have to filter their data (I assume). If the filters are always the same, that would be an even better solution.