How can we help?

New post
0

Table Component Issue - Column reference uses value from first row only

Avatar
Dor Ganot
None
Follow

Hi!

I'm trying to take the date values from column A in a Table component, then add (X - 1) months to them based on column B in the same Table, then show these new calculated dates in column C in the same table.

Here's the formula I tried:

DATE_ADD(DATE(!ColumnA, "yyyy-MM-dd"), month, !ColumnB - 1)

The results are all based on Column B of the first row only.

Example of actual results:
A                        B         C
2024-02-01        2          2024-03-01
2024-03-15        5          2024-04-15

Example of desired results:
A                        B         C
2024-02-01        2          2024-03-01
2024-03-15        5          2024-07-15

Any help would be highly appreciated!
Thanks :)

Date Votes

4 comments

  • Avatar
    Parker Selman Official comment

    Hi Dor,

    Thanks for reaching out! 

    This requires a bit of a complex formula, since the DATE_ADD function is required here, but this does not support arrays of month values. In order to pass the individual values in column B to the DATE_ADD function, we need to use a formula like this:


    This formula works as follows:

    1. Create a variable called "string" which is a string of your dates, a separator, and then the month value (NUMBERFORMAT is used here to keep the number formatting consistent) 

    2. Set up the DATE_ADD function, using SUBSTRING and INDEXOF to separate the date fields from the month numbers. Functionally, this is the same logic as your original formula, just going throw each row individually instead of applying one month value to your full column. 

    This formula can be found below for copy/pasting: 

    MAPFLAT(
    CONCAT(&Column: Date,"|",NUMBERFORMAT(&Column: Month)),
    "string",
    DATE_ADD(DATE(SUBSTRING($string,0,INDEXOF($string,"|")),"yyyy-M-d"),"3",SUBSTRING($string,INDEXOF($string,"|"))-1))

    I hope this helps! Please let me know if you have any questions,

    Parker

  • 0
    Avatar
    Dor Ganot

    Hi Parker,

    Thank you for your help!

    To be honest, I actually thought about doing exactly that, but I hoped there was a more elegant way to achieve this, as it seems like basic functionality to me...

    Is there a place in which I can suggest this feature to the team?

     

    Anyways I appreciate your help, I'll do it as you suggested :)

    Thanks!

    Dor

  • 0
    Avatar
    Parker Selman

    Hi Dor, 

    I'm glad this solution works, even if it is a bit cumbersome. In this case, it's difficult to dynamically add months through simpler means because a month is not a fixed period of time. If for example, we were adding increments of days to the columns, that could be done using simpler column addition. In this case, that's not possible, and at this time only DATE_ADD can add full months to values. 

    Feedback and feature requests can be logged here.  Feel free to walk through your use case in a post there and our team will keep the request logged for future improvements. 

    Cheers,

    Parker

  • 0
    Avatar
    Dor Ganot

    Hi,

    Appreciate your response and explanation!

    I think I understand better now.

     

    Thanks,

    Dor.

Please sign in to leave a comment.