How to format the cell values of a crosstable using ironpython.

[Prateek Rawat 3]

In my dashboard I have a crosstable which has some calculations happening based on user input through 2 input fields document properties. The input could be any number b/w 1 and 1000.

Few of the calculations(cell values) are formatted as percentage and few as numbers.

Issue is whenever someone enters a new value in the input field the formatting of the cell values goes away and it is not possible to format it for all the combinations b/w 1 and 1000.

Is there a way to fix this?

I have tried to do it with below ironpython script but it is not working for multiple columns:

from Spotfire.Dxp.Data import * 

from Spotfire.Dxp.Application.Visuals import *

from Spotfire.Dxp.Data.Formatters import *

myVis = myVis.As[Visualization]()

formatter = DataType.Real.CreateLocalizedFormatter()

formatter1 = DataType.Real.CreateLocalizedFormatter()

# Set the formatter category to Percentage

percent.Category = NumberFormatCategory.Percentage

number.Category = NumberFormatCategory.Number

# Set "Decimals:" option:

percent.DecimalDigits = 1  

percent.GroupSeparatorEnabled = True

number.DecimalDigits = 0  

number.GroupSeparatorEnabled = True

myVis.Formatting.IndexedRealFormatter['Sum(sales)'] = percent

myVis.Formatting.IndexedRealFormatter["Count(orders)"] = number

The above script formats the first column i.e Sum(sales) and does not format Count(orders).

Also, it doesn't work with aliases, for example:

myVis.Formatting.IndexedRealFormatter["Sum(sales) as [Total Sales]"] = percent #This does not work

Is there a workaround?

[Gaia Paolini]

Please can you show a reproducible example?

[Prateek Rawat 3]

hi Gaia,

In the attached image on the left hand side I have two input boxes A and B and on right hand side there is a cross table where there are few dynamic calculations based on the A and B.

I have set the format of no. of pages column to number with 0 decimals and % of pages to percentage with 0 decimals.

Issue is that when I change the values in A or B input boxes, the formatting of these columns goes away as shown in the screenshot which is there in the bottom.

Is there a solution to this?

I was trying to handle it using ironpython script but it doesn't seem to work.

[Gaia Paolini]

I guess it will depend on what the formulas are for your dynamic calculations based on A and B. If these were simply filters, the format would not change. From your screenshots unfortunately I cannot work out what these calculations are formulated as.

[Prateek Rawat 3]

A and B are document properties.

Below is one of the underlying calculations used for percentage column:

SUM(If(SN([no. of days open],${A}-1) > ${A} and SN([no. of days open], ${B}+1 ) <= ${B},[no.of open questions],0))/SUM([Total questions])

[Gaia Paolini]

I have created a cross table with this calculation as axis, and two document properties A and B. As I vary the document properties, the format remains the same. You could maybe open a support case, or share an anonymised Spotfire file here for further analysis.

[Prateek Rawat 3]

Hi Gaia,

In the crosstable I have put a dynamic alias name for below custom expression:

SUM(If(SN([no. of days open],${A}-1) > ${A} and SN([no. of days open], ${B}+1 ) <= ${B},[no.of open questions],0))/SUM([Total questions]) as [${A} and ${B} no. of days].

I observed that when I remove the dynamic alias name the formatting works fine on changing the input field values, but if I put the dynamic alias name back the formatting goes away whenever I change the values.

[Gaia Paolini]

it makes sense, as when A and B change, the display variable name changes, so maybe it is considered a new variable.

[Prateek Rawat 3]

Is there a workaround to this? May be using ironpython?

[Gaia Paolini]

would it be ok to put A and B in the title of the cross table instead? The columns could keep a standard fixed name and the title would tell you what values they refer to. It would save you a lot of trouble with IronPython.

[Prateek Rawat 3]

Sounds good. Thank you!