Is there a way for calculated value using a column from related tables to respect limit by marking

[Don Ashdown 2]

In Spotfire 7.5 I have two simple tables with two columns each

Cost

 

 

 

Category

Cost

 

 

A

1000

 

 

B

4000

 

 

 

Staff

 

 

 

Category

Staff

 

 

A

40

 

 

B

10

 

 

 

These are related by category with filtering enabled across the tables. I also have marking enabled on a treemap selector chart of the cost table.

For a bar chart of the average cost per staff by category using the value axis expressionSum([Cost].[Cost]) / Sum([staff].[staff]) and limited by marking everything works as expected. The bar chart displays the average correctly and the values staycorrect whether filtering by category or using marking to limit by category.

A text box with a calculated value using the same expression Sum([Cost].[Cost]) / Sum([staff].[staff])and also limited by marking however only displays the correct value for all data or whenfiltering by category. When marking is used to limit the categorythe calculated valueis incorrect (it divides by the total number of staff, not the marked subset).

It seems the marking isn't flowing to the related table for calculated values even though limit by marking is specified in the Data section of the calculated value and even though filtering works fine.

Is there a trick to getting this working for calculated values or is this a limitation of Spotfire Sample dxp attached.

[Shanmugam Saravanan]

Hi,

Please create a relations in datatable properties window for category column. Then you create detailed visualization. It will work.

Thanks

Shan

[Manoj Chaurasia]

Thank you for a very well prepared question. Unfortunately, the answer is that this is a limitation in Spotfire. In short, the quite small an specializedmini visualization in the textarea is not capable enough to handle the marking correctly when outside its main table. In this case, the main table for the calculated value visualization in the textarea is Cost and therefore it can not handle limit by marking in the Staff table, and instead uses all rows in that table. Since this is unexpected, the plot should perhaps indicated this limitation in the top panel.

[Nouarah Merah]

Hi,

You should log a support ticket to get this done as an enhancement request for future versions. In the meantime, this custom expression works for the calculated value in your text area:

case

when UniqueConcatenate([Category])="A" then Sum([Cost].[Cost]) / Sum(If([staff].[Category]="A",[staff].[staff]))

when UniqueConcatenate([Category])="B" then Sum([Cost].[Cost]) / Sum(If([staff].[Category]="B",[staff].[staff]))

end

(see attachmentrelated_tables_calculated_value_test_2.dxp)

Hope this helps.

Sihem

[Don Ashdown 2]

Thanks for the interesting approach to working around this issue. My sample is however a very much cut-down demonstration. The real dxp has more than 20 categories which can be marked in any combination so the case statement approach won't work there.

 

I have asked our support people to log a support ticket.

 

Don

[Keyur Parekh 2]

Hismerah,

I have come across the same situation where I want to drive my calculated values in text area from markings on the bar chart. I checked the .dxp example and saw that it can be done however when I can not locate the custom expression you have used. I checked the text area category axis however there is no custom expression written there. Can you please help me to locate where you have put this so that I can try to relate my case with yours

 

Best,

Keyur Parekh

[Daniil Terentyev]

Hi, Tibco Team,

 

Also this issue relates to KPI Chart. Not only to calculated value in Text Area.

 

4 years passed. In 10.3.3 LTS everything is the same as topic-started described :(((

Are you going to do anything with it

 

 

Regards,

Daniil.