Forum Discussion

Ejykso's avatar
Ejykso
Helper I
3 years ago

Custom Date Period

Hi All,

 

I have a problem with my custom date period. 

I'm using this:

var _today = today()

Return 

union ( 
addcolumns (

calendar(date(year(_today), month (_today), 1), _today)

,"type", "mtd", "order", 1

), 

addcolumns(

calendar(date(year(today),quarter(_today),1), _today)

,"type", "qtd", "order", 2

However, my qtd is not working but my mtd is working.  QTD is starting from feb which is not correct. 

 

Thanks for your help. 

 

2 Replies

  • ppm1's avatar
    ppm1
    Solution Sage
    3 years ago

    Why do you need to union these tables as it looks like they overlap?  In any case, here is a way to get a date range from the start of the current quarter.

     

     CALENDAR ( DATE ( YEAR ( _today ), FLOOR(MONTH ( _today ),3)+1, 1 ), _today )
     
    Pat
     
  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi Ejykso 

    try like:

    Table = 
var _today = today()
VAR _quarterstart = 
DATE(
    YEAR(_today), 
    (QUARTER(_today)-1)*3+1,
    1
)
Return
union (
    addcolumns (
       calendar(date(year(_today), month (_today), 1), _today),
        "type", "mtd",
        "order", 1
    ),
    addcolumns(
        CALENDAR(_quarterstart, _today),
        "type", "qtd",
        "order", 2
    )
)

     

    it worked like:

     

    it would be easier with Time Intelligence Functions.