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:
Clicks
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
Leads
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!
