How can we help?

New post
0

How to simplify long and complex formulas using variables or functions?

Avatar
Carlos Kynäslahti
None
Follow

Hi,

I've been building a dashboard which uses relatively simple, yet long winded formulas for calculating percentages and comparisons using past data. 

I'm starting to have issues with understanding how everything works because the formulas are so long (up to 5-6 rows). What is the best practice for slicing up formulas into more manageable parts? I'd like to use variables but it seems I'd have to create a separate Klip just to store and manage them, is this correct? Then a new user input component for defining each variable? Or can I just somehow open up a list of variables inside the editor and assign them values there?

 

Regards,

Carlos

Date Votes

7 comments

  • Avatar
    Janice Janczyn Official comment

    Hi Carlos,

    The only way to set a variable for general use is with a User Input Control component and these can optionally be hidden in multicomponent klips. A better choice may be to take advantage of the formatting capabilities provided with the new type-in editor. You can insert new lines (shift-enter) and indent (spaces) your formulas to improved their readability. One best practice is to align the parameters in your functions so you can easily tell which parameters belong to which functions, for example:

       date_convert( group( datevalue( daterange( date_startof( today(), year ),
                                                                              today() ),
                                                          "yyyy MM")),
                              "yyyy MM","MMMM yyyy")

    IMPORTANT NOTE: Depending on your scenario, using REFerences may be an option, however, these should be used carefully (and very importantly, not over-used) to avoid circular REFerences and degraded performance. REFs can be useful for simplifying the readability and maintenance of formulas, however, because REFs are fully expanded (internally) and calculated, they do not provide any performance savings.  Depending on their use, they may actually result in slower performance. A good practice is to consider what the fully expanded formula would look like and assess whether there is a more efficient formula.

    Thanks,

           Janice

  • 0
    Avatar
    Carlos Kynäslahti

    Hi Janice,

     

    Great to hear I can just indent the code with the new editor, that makes the everything much more readable. 

    I'm already using References to create comparisons and it's very helpful. Good to know that there's a potential drawback.

    I guess what I'm really looking for is more extensive and better variable management, similar to what you'd have when writing code in a "real" development environment (C#, JavaScript etc.). I hope Klipfolio will in the future consider implementing an easier and more manageable way of dealing with variables. 

    Edit: With the possibility of adding indentations, it would also be nice to be able to add comments to the Klipfolio formula :)

     

    Thanks again,

    Carlos

  • 1
    Avatar
    Meggan King

    Hi Carlos -

    Good news - you can add comments to formulas! You can use the following syntax:

    // comment here

    or

    /* comment here */

     

    We have had requests for "real" variables so that is captured with the product team as a future consideration.

     

     - Meggan

  • 0
    Avatar
    Carlos Kynäslahti

    Hi Meggan,

    Fantastic! Adding comments really helps with creating and managing Klips. I'm glad to hear that I'm not the only one looking for better variable management :)

     

    Thanks,

    - Carlos

  • 1
    Avatar
    Carlos Kynäslahti

    If anyone else happens to stumble upon this wondering how to make your code more readable, Klipfolio has now introduced a new feature which automatically indents your code. Either click the three little dots on the right side of the editor or press ALT+F.



  • 0
    Avatar
    Janice Janczyn

    Glad you like our auto-formating, Carlos!  We're pretty excited to offer this new capability. Try out the line numbers too--this is particularly handy when working with long formulas.

    Happy klip-building!

    Janice

  • 0
    Avatar
    Joe Williams

    Formatting a formula is way too overzealous.  It adds far too many new lines.  How is this "formatted"  formula any better than a block of text? 

    if(
     lookup(
     !'79eef5fc-10',
     select(
     DATEVALUE(
     DATE_ADD(
     date(
     slice(bfa069a1cb30a9ac6920ee23265f68c8@Daily FOH Closing Checklists Re,B:B;),
     "MM/dd/yy HH:mm"
     ),
     "6",
     -5
     ),
     "MM/dd/yy"
     ),
     slice(bfa069a1cb30a9ac6920ee23265f68c8@Daily FOH Closing Checklists Re,A:A;) = "Closing Cashier Checklist"
     ),
     slice(bfa069a1cb30a9ac6920ee23265f68c8@Daily FOH Closing Checklists Re,J:J;)
     ) > 0,
     1,
     0
    )
Please sign in to leave a comment.