How can we help?

New post
0

Stacked bar chart with 2 categories

Avatar
Cara Valle
None
Follow

Hey everyone, 

 

I'm trying to recreate a chart I've made in Salesforce that shows how many qualified leads are driven from each marketing channel.

I'm having a little trouble with grouping the data to make this work in Klipfolio - any suggestions?

 

Here is a screenshot of the chart I'm trying to replicate: 

 

And here is an example of the data source I'm working with: 

Source                        Status              Count

Email Marketing         Attempting          50

Email Marketing         Qualified             45

Organic Search          Attempting         15

Organic Search          Nurture              5

Organic Search          Qualified            40

Paid Social                 Attempting         30

Paid Social                Nurture               30

Paid Social                Qualified             30

Date Votes

4 comments

  • 2
    Avatar
    Oscar Rocha

    Hey Cara

    I'll solve that with the most important formula in Klipfolio wich is a combination of LOOKUP, GROUP , GROUPBY

    1.- You need to create the bar/line chart with 3 series

    2- Invert the Axis

    3.- And Stack bars by percentage

    4.- On X axis - type a formula of GROUP(!soruce)

    and on each serie

    5.- Type the formula like this changing the Status text

    LOOKUP( X Axis,  GROUP ( SELECT( !source,!status="Attempting")), GROUPBY(SELECT(!source,!status="Attempting",SELECT(!count,!status="Attempting")))

    that will get the result you are looking for.

     

    for more references check this article

    https://www.klipfolio.com/blog/most-important-formula-in-klipfolio

     

  • 1
    Avatar
    Cara Valle

    Thanks Oscar! I appreciate the help.

  • 1
    Avatar
    Janice Janczyn

    Hi Cara,

    There's a simpler approach using our Actions menu to

    • filter out the header rows (no need to SLICE every data reference) and
    • group the x-axis  and aggregate each series (no need to use GROUP & GROUPBY).

    The key is to ensure the data in each subcomponent is aligned, with the same number of items in the same order. Using your sample data,

    1. Set the X Axis to point to column A (Source), so your formula is simply @A:A. Then on the Properties tab, click Add Filter, unselect everything except Source (the header value) and click Exclude. This will filter out the header row. Then select Group repeating labels to group the Source values.
    2. For your first series, Attempting, your formula will be IF( @B:B="Attempting", @C:C, 0). On the Properties tab, set Aggregation to SUM. This is the equivalent of using GROUPBY. The IF construct ensures the Attempting series data is aligned with the X Axis, returning the Count value (column C) for each row where Status (column B) equals Attempting, otherwise 0 is returned.
    3. Add 2 series for Nurture & Qualified and repeat the same IF formula, replacing the check for Attempting with Nurture & Qualified respectively.

    If you needed to filter out any other data (for example, if there were a column D for Region), instead of using SELECT, you would

    1.  Go to Bar/Line Chart in the component hierarchy and click Add Hidden Data. This will add a Data subcomponent to your klip. 
    2. The formula for the hidden data would point to column D, so simply @D:D. Again, the data in this hidden data must align with the other subcomponents. On the Properties tab, click Add Filter and filter as needed.

    Further details on using Actions are available in our Actions article.

    Happy Dashboarding!
                                 Janice

  • 0
    Avatar
    Cara Valle

    Thank you, Janice! This got me really close to what I need.

    I do have a second data source that I am trying to incorporate into the chart as well - one for Leads and another for Contacts in Salesforce.

    When I created the chart with 1 data source originally it worked great, but when I added in the second data source things got messy.

    This is probably because the second data source doesn't have the same amount of values for the x-axis, and it also has some different values as well.

     

    So while the first data source looked like this: 

     

    Source                        Status              Count

    Email Marketing         Attempting          50

    Email Marketing         Qualified             45

    Organic Search          Attempting         15

    Organic Search          Nurture              5

    Organic Search          Qualified            40

    Paid Social                 Attempting         30

    Paid Social                Nurture               30

    Paid Social                Qualified             30

     

    The second one looks more like this: 

    Email Marketing         Prospect          5

    Email Marketing         Converted           10

    Organic Search          Prospect       15

    Organic Search          Converted           40

    Paid Search               Nurture              10

    Paid Search               Qualified            5

     

     

    Any recommendations on what I can do to make this work?

     

    Thanks,

     

    Cara

Please sign in to leave a comment.