Spotfire, Calculated Columns to show Percentages

[Philip Connell]

I hope you are well and that you can be of assistance. I have a table and I need calculated columns to show what percentage of 'No' 'Yes' and 'Empty' I have. I have attached a picture below.

Any help with the syntax would be greatly appreciated.

[Sean Riley]

Do you want the results in new columns in that Cross Table you have If yes, perhaps you can share your dxp or sample data set which shows your exact configuration.

 

Or do you want to create calculated columns that calculate those totals If you want calculated columns, you would expressions like these (i made assumptions of the data table structure that the cross table is based on, so you might need to modify):

 

Count(If([Consent Indicator]='NO',[CUSTOMER_ID]) OVER ([Country],[Total Amount in Country])

/

Count([CUSTOMER_ID]) OVER ([Country],[Total Amount in Country]) as [% NO]

 

Count(If([Consent Indicator]='YES',[CUSTOMER_ID]) OVER ([Country],[Total Amount in Country])

/

Count([CUSTOMER_ID]) OVER ([Country],[Total Amount in Country]) as [% YES]

 

Count(If([Consent Indicator]='' or [Consent Indicator] is null,[CUSTOMER_ID]) OVER ([Country],[Total Amount in Country])

/

Count([CUSTOMER_ID]) OVER ([Country],[Total Amount in Country]) as [% Empty]

[Philip Connell]

@Sean Riley: Thank you so much for taking the time to write an answer butafter alot fo hard work I got there on my own. The code is below 

 

 

 

_{Count([CUSTOMER_ID]) as [Number of Customer], Count([CUSTOMER_ID]) THEN [Value] / Sum([Value]) OVER (All([Axis.Columns])) as [Percentage]}

[Sean Riley]

Excellent! Yes, so that looks like the solution for the first approach of getting it right in the cross table. Nice job!

[gurusai sankar]

Hi Philip ,  can you please help on below spotfire query. https://community.spotfire.com/questions/how-get-grand-total-value-all-rows-or-else-how-fix-empty-values-all-rows-except-grand