OVER functions with external / in-database data

[Mark Herrmann]

Hi there,

I have a simple bar chart which uses the following expression on the y-axis to show the sum of [badBin] as a stacked bar of [binCode]s ([binCode] is on the color axis):

Sum([badBin]) / Count() Over (Parent([Axis.Color]))

 

Note the data is imported in the above example. I'd like to keep it external though which breaks the calculation though (Could not find function 'Parent').

I believe it has got to do with this but when trying to adjust the formula using THEN the calculations is no longer correct:

 

Can't get my head around this and also this did not help for me, anyone's got an idea

Thanks,

Mark

[Khushboo Rabadia]

There is a workaround to achieve this requirement in Spotfire without over expression:

1) Addthe same data table again as external say table2. table1 is already added in as external table.

2) Create data table relation between table1 and table2 on a primary or unique key

3) In above bar chart, use sum([badBin])/count([table2].[Wafer])

4) Remove all column matches between both tables except on Wafer column or atleast remove column match on Bincode column.

5) If you intend to use filters, create atleast one filter from both tables so they will appear in Filter Panel. Go to filter panel, besides table2 click on "Filtering in related data tables" icon, select "Include Filtered Rows only" from table1

Now, you will see the same result as seen with imported data using over expression.

[Mark Herrmann]

Thanks for sharing this workaround! Do you know how Spotfire would handle the duplicated external data source Would the same query be fired against the data source a second time

[Khushboo Rabadia]

I think it will fire query on second data source using x-axis column and retrieve count in Y-axis. You can check it either on external data source whether which query is fired or in spotfire client debug log (Spotfire.Dxp.SupportDiagnostics.log)--------------------------------------------------------------------------------------------------

How to enable Debug Logging - Spotfire Professional Client

--------------------------------------------------------------------------------------------------

1. Open Spotfire Client

2. Browse to "Help > Support Diagnostics and Logging" > Logging tab

3. Set the Log level to "Debug" and make a note of the "Log file" location

4. Click on OK

--------------------------------------------------------------------------------------------------