Xcelsius-Pivot Table Functionality



Xcelsius-Pivot Table Functionality

(Click on the images to Zoom)

As we know Xcelsius does not support ‘Pivot Tables’, so to get around this we need to use a combination of Sumif’s ,Concatenation and Combo boxes. ..


If we have a table which looks something like this.

We want to have the functionality and view of the pivot table in excel as shown in the picture.

This is the Dashboard that I’ll be using for this example. Here I have used two combo boxes with labels’ being picked from the Manager’s Column and the Employee Column. The Insertion Type as ‘Label’ and the destination as the two cells marked as ‘Manager’ and ‘Employee’ on the excel sheet.(Colored Black in Excel Sheet)

Here I have used a ‘Spread Sheet table’ component. In Properties >Display Data I have used the data highlighted in the sheet. Also we need to ‘Uncheck’ the rows in the ‘Behavior’ Tab of properties so that we do not have a selected row in the ‘Spread Sheet table’.

Now we go to the table: here we need to add another column ‘KEY’ which is a concatenation of the three columns as shown. We also need to add a similar key in the display table of the sheet as shown.


Once this is done add ’SUMIF’ as shown in the cells as shown.


And then we simply preview


  1. gravatar

    # by Anonymous - July 4, 2009 at 1:29 PM

    Good one...

  2. gravatar

    # by Anonymous - July 6, 2009 at 8:57 PM

    Hey can you share this particular dashboard with me..plz.. my email id is mahrung@hotmail.com

  3. gravatar

    # by Anonymous - July 12, 2009 at 3:48 PM

    Can you do a video on what you did and put it on youtube.com. Emaile of geoffreycraigkeane@gmail.com

  4. gravatar

    # by Analyst - July 20, 2009 at 10:32 PM

    I will do a video on this and put the link here..but for the time i have mailed you the dashboard...

  5. gravatar

    # by Anonymous - February 4, 2010 at 10:20 AM

    This example has helped me a lot. Thank you for putting it up. Is there a way this example can be extended to have an "ALL" combo value for the Manager and Employee? My users would like to be able to see chart containing combined data.

    Thanks in advance for your help.

  6. gravatar

    # by Analyst - February 8, 2010 at 5:05 PM

    Hi
    To answer you question ..the simplest way would be to simply add an "ALL" manager(dummy row) and an "ALL" Employee. Then use the same process as above.
    the values will simply be a sum of the respective values.
    Let me know if this answers your question.

  7. gravatar

    # by Anonymous - September 22, 2010 at 8:11 AM

    hi, can you share the dashboard to me ? my email is ericyap_kl@yahoo.com.
    thank in advance for your help.

  8. gravatar

    # by Anonymous - March 1, 2011 at 3:37 PM

    Hi can you share me this dashboard,my email is ckmurthy1234@gmail.com.

    Thanks
    Krishna

  9. gravatar

    # by Anonymous - October 23, 2011 at 7:55 PM

    Hi
    How did you do that you don't have double entriebs on the combo selections ?
    Please would it be possible that you send me the dashboard to meithom@yahoo.de ?
    Tks and Best Regards,
    Thomas

  10. gravatar

    # by Analyst - November 2, 2011 at 12:18 PM

    Thomas,
    I have sent the file to your email account.

    Thanks,
    Analyst

Blog Widget by LinkWithin

Search this blog..

Loading