Forum Discussion

mtrevisiol's avatar
mtrevisiol
Helper V
4 years ago
Solved

Calendar with IF statement

Hi everyone.

I would like to create a Calendar table that contains all the dates of the previous 12 months + dates until today.

For example, if today is 3rd December 2021, the table would contain dates from 01/12/2020 to 03/12/2021.

 

Of course, if today is, for example, 05/01/2021, the table would contain dates from 01/12/2019 to 05/01/2021, so I need to turn back 2 years.

 

This is the formula I wrote:

MyCalendar =
var decreased_month = MONTH(TODAY())-1
RETURN
IF(decreased_month>0,
      CALENDAR(DATE(YEAR(TODAY())-1, decreased_month, 1), NOW()),
      CALENDAR(DATE(YEAR(TODAY())-2, decreased_month+12, 1), NOW()))
 
but I get the message "The expression specified in the query is not a valid table expression."
 
Thank you in advance for help!
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Samarth_18's avatar
    Samarth_18
    4 years ago

    Hi mtrevisiol ,

     

    Your question is not much clear to me but the code which you are trying needs to be written as mentioned below:

     

    MyCalendar =
VAR decreased_month =
    MONTH ( TODAY () ) - 1
VAR start_date =
    IF (
        decreased_month > 0,
        DATE ( YEAR ( TODAY () ) - 1, decreased_month, 1 ),
        DATE ( YEAR ( TODAY () ) - 2, decreased_month + 12, 1 )
    )
RETURN
    CALENDAR ( start_date, TODAY () )

     Thanks,

    Samarth

1 Reply

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    Hi mtrevisiol ,

     

    Your question is not much clear to me but the code which you are trying needs to be written as mentioned below:

     

    MyCalendar =
VAR decreased_month =
    MONTH ( TODAY () ) - 1
VAR start_date =
    IF (
        decreased_month > 0,
        DATE ( YEAR ( TODAY () ) - 1, decreased_month, 1 ),
        DATE ( YEAR ( TODAY () ) - 2, decreased_month + 12, 1 )
    )
RETURN
    CALENDAR ( start_date, TODAY () )

     Thanks,

    Samarth