0

Invalid formula?

The parts of this formula are all valid. However, when put together, the message "INVALID FORMULA" is displayed. The schema and table names have been sanitized, but the table is an aggregated count by date of some other data. I have tried variants without success.

 

(if(
    table.Dt1 = $currentDate,
    table.Count,
    0
) / (sum(
    if(
        between(
            table.Dt1,
            lookup(
                Date_Time.Business_Days.business_3_days_ago_date,
                Date_Time.Business_Days.date,
                $currentDate
            ),
            date(
                $businessDayAgo
            )
        ),
        table.Count,
        0
    )
) / 3)) - 1
4replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi Marc,

    1-What your current version?
    2- What is the exact datatype for both of  "table.Dt1" and "business_3_days_ago_date"

    we can communicate with more details. through a support ticket

    Thanks

    Like
  • Can you try this?

     

    (sum(if(
        table.Dt1 = $currentDate,
        table.Count,
        0
    )) / (sum(
        if(
            between(
                table.Dt1,
                lookup(
                    Date_Time.Business_Days.business_3_days_ago_date,
                    Date_Time.Business_Days.date,
                    $currentDate
                ),
                date(
                    $businessDayAgo
                )
            ),
            table.Count,
            0
        )
    ) / 3)) - 1
    Like 2
    • Hi Dylan Wan 

      That's exactly what I showed to Marc and this works 👍

      Like 2
  • It would be good to have this documented somewhere. I looked but could not find anywhere that describes the requirements for calculations within an aggregated formula field.

    Like
Like Follow
  • Status Answered
  • 5 mths agoLast active
  • 4Replies
  • 30Views
  • 3 Following

Product Announcement

Incorta 4.9 is now Generally Available (GA)!!!