My question is related to a functionality in spotfire that I want to achieve

[devika Mukherjee]

I have a slider(property control) in my dashboard using which I am displaying the data for Month/Quarter/Year in a cross table

Problem is when I click on Quarter, I want to show the month of that quarter as well as the quarter totals.

Now in Spotfire the Horizontal axis has a hierarchy of Year,Quarter when I add month to it, again it goes into NEST . I need that month to be a separate horizontal column

Please help if there is any solution as I am new to Spotfire.

Sharing what is expected to be done below:

 

 

 

 

 

 

 

2019(Year)

 

 

Account 1

Division

Jan

Feb

Mar

Quarter 1(Totals)

 

 

abc

123

1

2

3

 

 

 

pqr

456

4

5

6

 

 

 

xyx

789

7

8

9

[Khushboo Rabadia]

You cannot achieve this directly using cross table expressions. As you are already using slider property control, you can execute Iron Python script based on slider value change which would update expressions in document properties and then use those document properties in cross table

if Document.Properties["Drop"]=="Year":

Document.Properties["horizontalcols"]="[Yr]"

Document.Properties["cellvalues"]="Sum([sales]) as [sales]"

 

elif Document.Properties["Drop"]=="Quarter":

Document.Properties["horizontalcols"]="[Yr] NEST [Axis.Default.Names]"

Document.Properties["cellvalues"]="Sum(case when Month([Date])=1 then [sales] end) as [Jan], Sum(case when Month([Date])=2 then [sales] end) as [Feb], Sum(case when Month([Date])=3 then [sales] end) as [Mar], Sum(case when Quarter([Date])=1 then [sales] end) as [Q1], Sum(case when Month([Date])=4 then [sales] end) as [Apr], Sum(case when Month([Date])=5 then [sales] end) as [May], Sum(case when Month([Date])=6 then [sales] end) as [Jun], Sum(case when Quarter([Date])=2 then [sales] end) as [Q2]"Attached dxp for reference for sample Year and Quarter values. You can modify this script as per your requirement and add in expressions when Month value is selected.