Skip to main content
Groupes* CRM Analytics (fka Tableau CRM) *Question de Valerie Zivich
Valerie Zivich (Sequoia Financial Group) a posé une question dans * CRM Analytics (fka Tableau CRM) *
4 août, 19:57

Dataflow: How to create a date field - 6 months prior

Hello, 

I'm currently trying to create a date field in a dataflow to calculate the month end date 6 months prior to another field. For example: 

 

Existing_Date_Field

= 2026-06-30 

Desired_Result

= 2025-12-31 

 

I'm struggling to find a combination of functions that gets me this result. Has anyone tried to do something similar and could you share your SAQL expression?  

 

 

Thanks!

9 réponses
  1. Valerie Zivich (Sequoia Financial Group)
    5 août, 20:40

    Posting a solution incase anyone is trying to solve the same issue in the future. It may not be the most simplified solution, but it does give the correct results: 

     

    month_last_day(toDate(
    (case
    when string_to_number(date_to_string(toDate(Existing_Date_Field_sec_epoch), "MM")) <= 6
    then number_to_string(string_to_number(date_to_string(toDate(Existing_Date_Field_sec_epoch), "yyyy")) - 1, "0000")
    else number_to_string(string_to_number(date_to_string(toDate(Existing_Date_Field_sec_epoch), "yyyy")), "0000")
    end)
    + "-" +
    (case
    when string_to_number(date_to_string(toDate(Existing_Date_Field_sec_epoch), "MM")) <= 6
    then number_to_string(string_to_number(date_to_string(toDate(Existing_Date_Field_sec_epoch), "MM")) + 6, "00")
    else number_to_string(string_to_number(date_to_string(toDate(Existing_Date_Field_sec_epoch), "MM")) - 6, "00")
    end)
    + "-" + "01",
    "yyyy-MM-dd"))
Rédiger une réponse...
0/9000