Combining multiple data sources in a chart both dynamic and static

I am trying to make a line chart that shows click, leads, sales calls, and sales. I have several different spreadsheets and APIs I am using to include historic data and update daily for ongoing data.

I have a date picker set up to choose date start and date end using the user import component and hidden data. This seems to be working as expected.

I am having trouble totaling data for clicks and leads over time. It appears clicks and leads not totaling correctly. I want to show the total clicks and leads for each day in a value pair component and on a line chart and have it dynamically total each for each day. The klip looks like this:

Here is what i have so far:


1. Google Sheets: I have historic data from a google sheet from the entire last year. A row is each day and the clicks are totaled in a column of this sheet. This is a static sheet.

2. ClickMagick: Rest URL set up. I have parsed the data and each click gives the date of the click and each user has a unique Id attached to it so I can use CountIF based on the Id column to filter the clicks to the right customer. this is a dynamic data source. 

I have formatted both of these to be the same columns to hopefully be able to total this data.  

I am using the Array function to get the total and select to filter by the condition. (ARRAY(Select(GoogleSheet @ClickColumn, GoogleSheet @ClickColumn = ClickColumn Id), SELECT(ClickMagick @id column = 2424665)))

The clicks are not totalling as expected


1. Rest URL to a CRM which provides each lead. Leads are assigned by a column providing the clients name. This is dynamic

2. Static Excel CSV file formatted similar columns as the RESTURL. This is to update historic data.

I am using the Array to combine both files and the Select to filter the values by the condition to get the right number of leads each day)

The leads not showing at all. I know the leads should have a 1 for 11/22 as I see the data but can not get it to show up on the line chart or value total

I am not worried about sales and calls right now as I can figure that out once I have these to metrics correctlly assigned.

Thanks for your help!


  • Avatar
    Shima Beigzadeh Official comment

    Hi Andrew, 

    Thanks for posting into our community post! 

    I was able to look into your Klip and see what formula you have been set up. 

    The number of items for X axis and each series should be equal. For example if you have an Array (@date1,@date2,@date3) for 30 items in your X Axis, then in each series we should also have 30 items. 
    For example for Clicks, you would make an Array in the same order of your X Axis Array like below. 
    Array (@clicks1,@clicks2, repeat (blank(), count(@data3))

    Please note that if you don't have Clicks in DataSource3, then you would need to fill that spot with BLANK() for alignment issue. 

    I have created one Klip Example in your Account. Please have a look and if you have further questions, please send us an email to support@klipfolio.com



  • 0
    Andrew Dunbar

    OK thanks I think it maybe best to start over and redo this as I have a lot of data sources to include and make blanks for. I appreciate your example I will model this and see what I can come up with. Appreciate the help!

Please sign in to leave a comment.