How can we help?

New post
0

Setting a variable based on another variable

Avatar
Stuart Macdonald
None
Follow

We have a set of data with multiple rows, with one column being a date. We are wanting to allow the user to select a date (date picker User Input Control is fine), then display two tables of data as a result:  one table containing records that match the selected date, and the second table displaying records that match the whole week the selected date is in. 

I had assumed I could take the chosen date and store this as Selected_date, then from this calculate the date that is the end of the week to use as a limit in my second table: for example DATE_ENDOF($Hours_worked_date,week,0,Monday)

I can calculate that date OK, but how do I store this as a variable? I thought using SET, but no joy. Hopefully just a syntax issue. Thanks heaps for looking. 

Date Votes

6 comments

  • 0
    Avatar
    Janice Janczyn

    Hi Stuart,

    You can add 2 hidden UI components to your date picker klip to set start and end of week date variables. These UI components calculate their variables using the formulas you suggested:

         DATE_STARTOF( $Hours_worked_date, week, 0, Monday )
         DATE_ENDOF( $Hours_worked_date,week, 0, Monday )

    but because they're hidden they aren't intended to be modified by a user.

    The SET function sets a variable for use only within the scope of the SET function, that is, the formula in the 3rd parameter.

    I hope this helps.  Happy dashboarding!

    Janice

  • 0
    Avatar
    Stuart Macdonald

    Hi Janice, thanks for the reply, however I'm not sure I follow. As I mentioned I understand how to calculate the new dates based on the result of the date picker, but I am unsure how to then use those new dates as limits on a table. My understanding is that to use them I'd have to have them as variables. 

  • 0
    Avatar
    Janice Janczyn

    Hi Stuart,

    Yes, you'll need to use a filter to check the variables set by your hidden UI components. Add a filter to the dates column in your table or add hidden data for the dates if you don't need the dates displayed in a column. Make sure the dates are formatted as Date/Time, then add a filter with 2 conditions (assuming your hidden UIs set variables named startOfWeek and endOfWeek):

         On or after $startOfWeek
         On or before $endOfWeek

    Set the filter Match All and you should be all set!

    Thanks,
             Janice

  • 0
    Avatar
    Stuart Macdonald

    That's the bit I am unable to get right....setting those two variables! 

  • 0
    Avatar
    Janice Janczyn

    Hi Stuart,

    The 2 variables are set by 2 hidden dropdown User Input components, added to your date picker klip, so that it becomes a multicomponent klip containing 3 UI klips:

         UI1 - date picker, sets the Hours_worked_date variable, visible,
         UI2 - dropdown, sets the startOfWeek variable, hidden with Values formula set to DATE_STARTOF( $Hours_worked_date, week, 0, Monday )
         UI3 - dropdown, sets the endOfWeek variable, hidden with Values formula set to DATE_ENDOF( $Hours_worked_date, week, 0, Monday )

    If this still isn't clear, please email support@klipfolio.com for further assistance.

    Thank you,
                  Janice

  • 0
    Avatar
    Stuart Macdonald

    Yeah, I'm still lost sorry. I don't see how any of that is defining the new variables so they can be used as a limit. I'll log it I guess, unfortunately the reason I ended up here was because previously when I've logged a call it was suggested I try the forum!  Thanks for your time though. 

Please sign in to leave a comment.