Cross Table subcategory Ranking

[Viji Dade]

Hi,

How to create a calculated column which show only one value for a subcategory and rest of the values as ## in Cross table.

[Viji Dade]

When there is more than one sub category then I would like to calculate only one value per sub category and rest of the rows as ## as shown in above screenshot.

[Olivier Keugue Tadaa]

Hi Viji

Let's consider the following data table

you can use this expression on your cross-table

first(If([sub category]=first([sub category]) over (Parent([Axis.Rows])),String([value]),"##")) as [Calculated Column]

 

Let me know if this works for you

[Viji Dade]

Hi Olivier, 

Thank you for your response. I have already tried this but it is not working in some cases. My data looks as below. In some cases Parent([Axis.Rows]) not taking the previous row value. Oly one sub category should have value when there are multiple values and rest of the value should be ##.

Category	SubCategory	Predicted	Observed	Value	Expected Value
Sev1	Sev1-Sub1			0	0
Sev1	Sev1-Sub2		ob3	0.1	0.1
Sev1	Sev1-Sub3		ob1	3.5	3.5
Sev1	Sev1-Sub3		ob2	3.5	#
Sev1	Sev1-Sub3	Pre1		3.5	#
Sev1	Sev1-Sub4		ob3	89.4	89.4
Sev1	Sev1-Sub4		ob1	89.4	#
Sev1	Sev1-Sub4		ob5	89.4	#
Sev1	Sev1-Sub4	Pre4	ob4	89.4	#
Sev1	Sev1-Sub5			0	0
Sev2	Sev2-Sub1	Pre2	ob4	0.2	0.2
Sev2	Sev2-Sub2		ob3	0.5	0.5
Sev2	Sev2-Sub2		Ob1	0.5	#
Sev2	Sev2-Sub3			0.5	0.5
Sev2	Sev2-Sub4	Pre1	Ob1	99.2	#
Sev2	Sev2-Sub4		Ob3	99.2	#
Sev2	Sev2-Sub4		Ob3	99.2	99.2
Sev2	Sev2-Sub4			99.2	#
Sev2	Sev2-Sub4		Ob4	0.1	0.1
Sev2	Sev2-Sub5		Ob2	0.1	0.1

Thanks,

Viji

[Olivier Keugue Tadaa]

Okay, thanks for the additional information. Let me have a look. I hope there is no other important information I need to know...

BTW,  can you provide us with your raw data structure along with the expected result?  That will be more efficient

Edited August 9 by Olivier Keugue Tadaa

[Viji Dade]

Category    SubCategory    Predicted    Observed    Value    Expected Value
Sev1    Sev1-Sub1            0    0
Sev1    Sev1-Sub2        ob3    0.1    0.1
Sev1    Sev1-Sub3        ob1    3.5    3.5
Sev1    Sev1-Sub3        ob2    3.5    #
Sev1    Sev1-Sub3    Pre1        3.5    #
Sev1    Sev1-Sub4        ob3    89.4    89.4
Sev1    Sev1-Sub4        ob1    89.4    #
Sev1    Sev1-Sub4        ob5    89.4    #
Sev1    Sev1-Sub4    Pre4    ob4    89.4    #
Sev1    Sev1-Sub5            0    0
Sev2    Sev2-Sub1    Pre2    ob4    0.2    0.2
Sev2    Sev2-Sub2        ob3    0.5    0.5
Sev2    Sev2-Sub2        Ob1    0.5    #
Sev2    Sev2-Sub3            0.5    0.5
Sev2    Sev2-Sub4    Pre1    Ob1    99.2    #
Sev2    Sev2-Sub4        Ob3    99.2    #
Sev2    Sev2-Sub4        Ob3    99.2    99.2
Sev2    Sev2-Sub4            99.2    #
Sev2    Sev2-Sub4        Ob4    0.1    0.1
Sev2    Sev2-Sub5        Ob2    0.1    0.1
 

[Viji Dade]

[Vincent Thuilot]

Hello,

what about something like this

Assuming that you are fine with adding RowId, you can compute the lowest rowId by subcategory like this:

CASE  
WHEN [RowId()]=Min([RowId()]) OVER (INTERSECT([SubCategory])) THEN [Value]
ELSE "##"
END

and then use FIRST in the cross-table, after sorting by RowId it should work out fine.

You can later hide the RowId column with an IronPython Script if this is necessary:
something like this:
 

# 'vis' is a script parameter

# Import Modules

from Spotfire.Dxp.Application.Visuals import *

# Set row header column width to 0 (i.e. first column)

vis.As[CrossTablePlot]().RowHeaderWidths.Item[0]=0

I attach the dxp i quickly worked on, hope it helps!

 

Data - Sheet1.dxp

[Viji Dade]

Hi Vincent, what I have shared you was the cross table data and for each cross table row we have many table rows for which we are calculating percentage in cross table. so the RowId is not working in cross table.