SET function

What's in this article?

  • Learn how to use the SET function to create a Klip.
  • Step-by-step instructions.
  • Audience: Intermediate.

SET( variable names, values, expression)

The SET function sets the variables specified by the first parameter (must be typed as text) to the values specified by the second parameter and then performs the formula (which uses the first parameter variables) specified by the third parameter.

How to use the SET function

The SET function lets you use a variable in a formula, typically making the formula becomes easier to read and understand.

In this example, the SET function assigns the following values.

  • setyear: DATE_UNITVALUE( DATE( A:A ) , MM/dd/yy, year)
  • setmonth: DATE_UNITVALUE( DATE( A:A ) , MM/dd/yy, month)

This example uses the following data source.

Create a user input component

Build a user input control component. For more information, see Creating a filter to display related data.

For our example,

  • the variable name is docset
  • the formula for the Values sub-component is ARRAY( 30 , 365 )
  • the formula for the Labels sub-component is ARRAY( month , year )

Create the X-Axis

  1. Create a Bar and Line Chart and go to the Klip Editor. 
  2. Select the X-Axis: Untitled component from the Component Tree.
  3. Select data or start formula and click Insert Function.
  4. From the Function menu, select Data Manipulation > SET.
  5. The SET formula displays SET (variable names, values, expression)

  6. Select variable names and click Insert Function > ARRAY.
  7. Select data, click Insert literal string or number and type "setmonth" (without the quotes) variable and click Apply. Repeat for the setyear variable.
  8. Select values and insert the DATE_UNITVALUE function.
  9. Select values, then select the column that contains the date.
  10. If your data source has a row header, wrap the SLICE function around your selection. Wrap the DATE function around your selection. Note the DATE function converts the date to epoch time.
  11. Select format, click Insert literal string or number, type the format of the date as it appears in your data source and click Apply .
  12. Select unit and select month from the Insert an Option menu.
  13. Use the JOIN and ARRAY functions to add another variable: 
    • Select DATE_UNITVALUE, wrap the JOIN function around your selection, then wrap the b>ARRAY function around this.
    • Copy the DATE_UNITVALUE part of the formula to data and change month to year.
    • Select the second DATE_UNITVALUE, then wrap the JOIN function around it.



  14. With the variables defined, they can be used in a formulas.
    • Select expression, then insert the GROUP function.
    • Select values, then insert the SWITCH function.
    • Select data and insert the variable created in the user input control component, for our example, docset.
    • Select case, then select Insert literal string or number, type the monthly value from the user input control component (for our example, 30) and click Apply. 
    • Select value, then select Insert new variable and create and insert a new variable called setmonth.
    • Select setmonth and wrap the ARRAY function around it.
    • Select ARRAY and wrap the CONCAT function around it.
    • Select the second data, then Insert literal string or number and type " - " (without the quotes) and click Apply.
    • Select data and insert the ARRAY function. Select data, then Insert new variable and create and insert a new variable called setyear.
    • Select SWITCH, then case, select Insert literal string or number and type inthe yearly value from the user input control component (for our example, 365) and click Apply
    • Select value then insert the ARRAY function. Select data, then Insert new variable and insert the variable, setyear.

    Create the Series component

  15. Copy the X-axis formula to the Series:Untitled sub-component.
    • Select GROUP and replace with GROUPBY.
    • Select measure, then the Value column (for example, column B). If the column has a row header, wrap the SLICE function around the selection.
    • From the Properties panel, assign a name to the Series and X-Axis.

What's next?

Did you have trouble with the material in this article?

These articles may help:










Functions

  1. Introduction: Using formulas
  2. Data Manipulation functions
  3. Logic functions
  4. Math functions
  5. SELECT function
  6. Text Functions
  7. Date and Time Functions
  8. Statistics Functions
  9. ABS function
  10. AND function
  11. ARRAY function
  12. AVERAGE function
  13. BETWEEN function
  14. BLANK function
  15. CAPITALIZE function
  16. CEILING function
  17. CONCAT function
  18. CONTAINS function
  19. COUNT function
  20. COUNTALL function
  21. COUNTBLANK function
  22. COUNTDISTINCT function
  23. COUNTIF function
  24. COUNT_DAYS function
  25. COUNTNUMERIC function
  26. CUMULATIVE function
  27. CUMULATIVE_DIFFERENCE function
  28. DATASOURCE function
  29. DATE and DATEVALUE functions
  30. Using Date and Time Formats
  31. DATERANGE function
  32. DATE_ADD function
  33. DATE_CLOSEST function
  34. DATE_CONVERT function
  35. DATE_ENDOF function
  36. DATE_IN function
  37. DATE_SET function
  38. DATE_STARTOF function
  39. DATE_UNITVALUE function
  40. FLOOR function
  41. ERF function
  42. FIRST function
  43. ERFC function
  44. GROUP and GROUPBY functions
  45. IF function
  46. IN function
  47. INDEXOF function
  48. JOIN function
  49. LAST function
  50. LASTINDEXOF function
  51. LEFT function
  52. LOOKUP function
  53. LOWER function
  54. MAP function
  55. MAX function
  56. MIN function
  57. MEDIAN function
  58. MOD function
  59. MODE function
  60. Moving Average (Cumulative)
  61. Moving Average (Exponential)
  62. Moving Average (Simple)
  63. NORMSDIST function
  64. NOT function
  65. NOW function
  66. NUMBERFORMAT function
  67. OR function
  68. PADVALUES function
  69. POWER function
  70. RANK function
  71. REMOVE_EMOJI function
  72. REPEAT function
  73. REPLACE function
  74. REVERSE function
  75. RIGHT function
  76. ROUND function
  77. SET function
  78. SLICE function
  79. SLOPE function
  80. SORT function
  81. SPLICE function
  82. STANDARDIZE function
  83. STDEV function
  84. STDEVP function
  85. SUBSTITUTE function
  86. SUBSTITUTE_REGEX function
  87. SUBSTRING function
  88. SUM function
  89. SUMIF function
  90. SWITCH function
  91. TEXT_REVERSE function
  92. TODAY function
  93. TRIM function
  94. TRUNCATE function
  95. UPPER function
  96. URLDECODE function
  97. URLENCODE function
  98. VARIANCE function
  99. VARIANCEP function
  100. YESTERDAY function
  101. ZTEST function

Feedback and Knowledge Base