Cross table in Spotfire

Can somebody help me with the following problem.

Given the data table:

Key     Rec     Period  KPIa    KPIb Key1    Rec1    Period1 x       z Key1    Rec2    Period2 y    Key2    Rec3    Period1 x       z Key2    Rec4    Period2 y    

In Spotfire, it is possible to create the following cross table:

           Sum(KPIa)      Sum(KPIb)            Delta          Period1 Period2 Period1 Period2  Period1   Period2 Key1    x       y       z               (z-x)/x   Key2    x       y       z               (z-x)/x   

Now there are two things I would want to change in this cross table but I can’t manage to figure out how:

1. Delta is a calculated column which is only valid for Period1. Is it possible to apply the extra Period1 and Period2 level only to certain columns of the cross table?
2. In the Sum(KP1b) column, Period2 is empty. Is it possible to hide this specific column? I tried using Properties->Data->Limit data using expression with ‘[KPIb] is not null’. However, this filters out all rows for which KPIb is not specified. As a result the entire ‘Period2’ column disappears.

So what I want is:

            Sum(KPIa)      Sum(KPIb)     Delta         Period1   Period2  Period1    Key1    x         y        z            (z-x)/x   Key2    x         y        z            (z-x)/x   

Thanks in advance.

1 Answers

Answers 1

@ user6076025 - Please check this solution and let me know if this helps.

I have considered X as 1, Y as 2 and Z as 3 for computation purpose.

I have unpivoted your data which is there in the first screenshot of your post and then created a cross table from the unpivoted data.

Attached are the screenshots for your reference.

Regards,

kps

If You Enjoyed This, Take 5 Seconds To Share It

• Facebook
• Twitter
• Google+
• StumbleUpon
• Digg
• Delicious
• LinkedIn
• Reddit
• Technorati