Using two data sources to combine data and revenue totals for current and previous months

This use case combines monthly revenue data from two different data sources (eg: Salesforce and Google Drive) AND combines two monthly revenue totals from the previous and current month.  For example, February's total includes the revenue amounts for January and February from both data sources.

Let's do an example using filtering.

To use filtering, your column data must evaluate to the same number of items, ie 12 months in column A  (eg: January 2018) and 12 values (eg: 10,000) in column B. This might mean you need to use 0 for months that do not yet have values.


Upload your two data sources. In my example, the service connectors used are Salesforce and Google Drive.


For the X:Axis formula, type ARRAY and point to the date column in your Salesforce data source, and then your Google Drive data source.  For example,:


If your date columns are not using the same date format, use the DATE_CONVERT function to change one of them.
Use the Properties panel to specify the incoming date and time format (say: MMMM yyyy) and the display format you want to see (say: MM yyyy), and remember to check off group repeating labels to get one instance of the month.  


Select Bar/Line chart, Properties panel, to add a hidden data sub-component to your Bar/Line chart.
For the hidden data sub-component formula, type ARRAY and point to the revenue amount column in your Salesforce data source, and then your Google Drive data source. Keep the data source order the same to ensure the data stays aligned.
This formula will return the revenue amount for each month.


Select the Series formula bar, and type & and select the name of your hidden data sub-component (&Data:Revenue). 
This is called a Results reference and returns the results after the formula has been executed and helps with your Klip's performance.  
Type + and then use the below ARRAY formula to add the previous month’s values to the current month’s values.  

&Data:Revenue + ARRAY( 0, SLICE( &Data:Revenue, 0, -1))

This formula is shifting the monthly values by 1. The 0 is taking into account that January will have no value added and the SLICE function (-1) is removing one value from the array of values.

You might also need to adjust the Label Rows/Angle to Auto for the X:Axis.



Please sign in to leave a comment.