Tips and Tricks

Check out our growing list of tips and tricks.

With these tips and tricks you can become more familiar with working in Klipfolio.

Klipfolio Tips:


Question 1: Why is my GROUPBY formula not returning the right result?
Question 2: How do I show a sub-set of data in a column?
Question 3: When I wrap my date column in the DATEVALUE function the date does not show as dd-MM-yyyy?
Question 4: How do I extract the text BkCatInTheHat from the text string BkPg,CatLT,BkCatInTheHat,4?
Question 5:: How do I display the numbers 1-10 on the X-Axis without referencing my data source file?
Question 6: Why does my JSON file not return a value for the field name, Monthly target?
Question 7: How do I convert my Salesforce date - 2014-03-05T08:45:59.000+0000 - that is currently in GMT time to -8.00 (PST) time?
Question 8: When I use the DATERANGE function to return week numbers and specify Monday as the start date, 18 May 2014 returns as week 20, and not week 21?
Question 9: How do I retrieve data for the last 30 days?
Question 10: How can I get the DATE_STARTOF function to group dates by week starting on Monday, instead of Sunday?
Question 11: How do I select the M.65+ field in my Facebook data source?
Question 12: A backslash in my Google Analytics data source is preventing me from bringing in my data. Is there anything I can do?
Question 13: Is it possible to retrieve historical data from Facebook Insights?
Question 14: Have a tip to share?

Answers:

Question 1: Why is my GROUPBY formula not returning the right result?

The GROUPBY function SUMs all values by default. When you are working with the GROUPBY function, it is recommended that you always evaluate the first and second arguments of the formula, i.e. values and measure. The total number of items for both arguments must return the same number of items. Therefore, if you are selecting data based on a condition, the condition must be in the first argument, as well as in the second argument.

For example, GROUPBY( SELECT( SLICE( C:C ) , (SLICE( B:B ) = "Exit clicks" ) ) , SELECT( SLICE( G:G ) , ( ( SLICE( B:B ) = "Exit clicks" ) ) ) , method )

In this example, the first argument (values) is selecting the dates from column C only for the values for Exit clicks that exist in column B. The second argument (measure) is selecting the values from column G only for the values for Exit clicks that exist in column B. When evaluated, the same number of items for values and measure are returned. Note: The SLICE function in this formula removes the row header for each column of data.

Question 2: How do I show a sub-set of data in a column?

To select a subset of data, use the SELECT function to specify an operator, such as "not equals to".

SELECT( B:B ( A:A ≠ “ACME” ) )

In this example, the values in Column B for ACME are not displayed.

Question 3: When I wrap my date values in the DATEVALUE function the date does not show as dd-MM-yyyy?

To see the date as dd-MM-yyyy you only need to select the column that contains the date and choose the date format dd-MM-yyyy from the Properties panel. Normally the input format is automatically detected but if this is not the case, you must select Custom and type in the date format as it appears in the data source. For the Output format, specify the format you want displayed. For more information on date formats, see this Oracle reference.

The DATEVALUE function is not necessary as the date is already in a human readable format. You only need to use the DATEVALUE function when a date is in epoch time which is used to perform a date calculation. For more information on working with dates, see Using date and time functions.

Question 4: How do I extract the text BkCatInTheHat from the text string BkPg,CatLT,BkCatInTheHat,4?

To extract BkCatInTheHat from BkPg,CatLT,BkCatInTheHat,4, you can use the SUBSTRING and INDEXOF functions.

The final formula is: SUBSTRING( A1 , ( INDEXOF( A1 , "," , 2 ) + 1 ) , INDEXOF( A1 , "," 3 ) )

Where A1 is the full text string of BkPg,CatLT,BkCatInTheHat,4

To build this formula, complete the following steps:

  • With select data or start formula selected, click Insert function, Text, SUBSTRING
  • With text selected, select the whole text string, BkPg,CatLT,BkCatInTheHat,4 (or cell A1 in this example)
  • With from selected, click Insert function, Text, INDEXOF
  • With text selected, select the whole text string, BkPg,CatLT,BkCatInTheHat,4 (or cell A1 in this example)
  • With search text selected, click Insert literal string or number and type ,
  • With occurrence selected, click Insert literal string or number and type 2
  • With INDEXOF selected, click Wrap current value in parenthesis
  • Click ..., select the + operator, Insert literal string or number, and type 1
  • Using Ctrl+C and Ctrl+V, copy and paste the INDEXOF part of the formula to to and change 2 to 3

Question 5: How do I display the numbers 1-10 on the X-Axis without referencing my data source file?

To display the numbers 1-10 on the X-Axis you can use the following formula.

CUMULATIVE( REPEAT( 1 , MAX( 10 ) ) )

You can then reference this formula in any Series formulas by selecting the REF (Insert reference to another component) button from the formula bar.

Question 6: Why does my JSON file not return a value for the field name, Monthly target?

In a JSON file type, to select a field name that contains a space, you must edit the XPath and use a function that will match the field name, for example:

/data/values/value/*[starts-with(name(.),"Monthly")]
/data/*[contains(name(.),"target")]

For more information on the XPath editor, see Working with XML/JSON data sources.

Question 7: How do I convert my Salesforce date - 2014-03-05T08:45:59.000+0000 - that is currently in GMT time to -8.00 (PST) time?

To convert the date to PST time, change the format string in your Salesforce query to:"yyyy-MM-dd'T'HH:mm:ss.SSSZ". For more information, see this Oracle reference.

Then use the following formula to change the date to PST time. 

DATEVALUE(DATE("2014-03-05T08:45:59.000+0000","yyyy-MM-dd'T'HH:mm:ss.SSSZ","GMT"),"MMM dd, yyyy HH:mm:ss","PST")

Question 8: When I use the DATERANGE function to return week numbers and specify Monday as the start date, 18 May 2014 returns as week 20, and not week 21?

To have 18 May 2014 return as week 21, you will need to subtract one day from the DATERANGE formula using the DATE_ADD function. The formula might look like this one.

DATEVALUE( DATE_ADD( DATERANGE( DATE_STARTOF( TODAY( "GMT+01.00" ) , week , 0 , Monday ) , DATE_ENDOF( TODAY( "GMT+01.00" ) , week , 0 , Monday ) , "M/dd/yy-ww" ) , day , -1 ) , "M/dd/yy-ww" , "GMT+01:00")

Question 9: How do I retrieve data for the last 30 days?

To retrieve data in a specific time period, you will need to use a date range parameter in your query. For more information on date range parameters, see Use date range parameters to modify data retrieval.

For example, in Google Analytics, your query might look like this one.

https://www.googleapis.com/analytics/v2.4/data?ids=ga%id&dimensions=ga%3Adate%2Cga&metrics=ga%3Agoal1Completions&start-date={date.tz('GMT-5:00').add(-30).format()}&end-date={date.tz('GMT-5:00').today}

Note: This query returns data in a Central Time Zone (GMT-5:00).

Question 10: How can I get the DATE_STARTOF function to group dates by week starting on Monday, instead of Sunday?

You can use the following formula: DATEVALUE(GROUP(DATE_STARTOF(DATE(data,"dataformat"),week,0, Monday)),"EEE dd-MMM"). Replace the "EEE dd-MMM" with the output format you want.

Question 11: How do I select the F.65+ field in my Facebook data source?

If you have a Facebook data source with fields like F.25-34, F.65+, use the ARRAY function to build an array of values. For example, ARRAY( F.25-34, F.65+, ... )

You will notice that you are not able to select the F.65+ field. To add this field, use the XPath editor to edit the path to this field. For example, click the XPath button and enter the following path: /data/values/value[@name='F.65+'].

In the case where the data source has multiple values for each page range, update the XPath to specify the value you want. For example, /data/values[1]/value[@name='F.65+'].

Question 12: A backslash in my Google Analytics data source is preventing me from bringing in my data. Is there anything I can do?

Typically, a back slash acts as an escape quotation mark in a CSV file. You can try filtering out the record from the returned data set by including a filter on the field where the back slash is located. For example, in your query, you might add a filter similar to this: &filters=ga:keyword!@\\. For more information related to Google Analytics filters see Using filters.

Question 13: Is it possible to retrieve historical data from Facebook Insights?

To retrieve historical data from the Facebook API, use the “since” and “until” parameters to specify a different date range.

For example, https://graph.facebook.com/*fbpagename*/insights/page_engaged_users,page_impressions/days_28?since={date.startOfYear.format()}&until={date.set(date.startOfYear.format()).add(90).format()}

This query returns data for the first 90 days of the year. Using this technique, you can then create four different queries to retrieve data for the entire year.

For example, https://graph.facebook.com/*fbpagename*/insights/page_engaged_users,page_impressions/days_28?since={date.set(date.startOfYear.format()).add(91).format()}&until={date.set(date.startOfYear.format()).add(180).format()}

This query returns data for the 2nd quarter.

Question 14: Have a tip to share?

If you have a tip to share with other Klipfolio users, send us an e-mail at support@klipfolio.com.

Related links:

Feedback and Knowledge Base