Forum Discussion

taek_onvi's avatar
taek_onvi
Cloud Apps
08-26-2022

Week to date as JAQL query

Hi,

I am trying to create a jaql query that replicates what I can filter on the dashboard using the UI - filtering a date range to 'This week and last week'. But in the jaql editor, it looks like 'weeks' are not accepted as a date level, so I was wondering the best way to do this.

I have currently tried:

 

 

{
"datasource": "reporting_data",
"metadata": [
	{
		"dim": "[dim_calendar.date]",
		"datatype": "datetime",
		"level": "weeks",
		"filter": {
			"last": {
				"count": 2,
				"offset": 0
			}
		}
	}		
]
}

 

 

 

dashboards & reporting

3 Replies

Replies have been turned off for this discussion
  • Angelina_QBeeQ's avatar
    Angelina_QBeeQ
    ETL
    08-26-2022

    Hi taek_onvi ,
    try to add firstday:

     

    {
"datasource": "reporting_data",
"metadata": [
	{
		"dim": "[dim_calendar.date]",
		"datatype": "datetime",
		"level": "weeks",
        "firstday": "mon",
		"filter": {
			"last": {
				"count": 2,
				"offset": 0
			}
		}
	}		
]
}

     

    Best regards,
    Angelina

    • taek_onvi's avatar
      taek_onvi
      Cloud Apps
      08-26-2022

      Hi Angelina,

      Thanks! The result gives me these values:

      "values": [
		[
			"2022-08-18T00:00:00"
		],
		[
			"2022-08-25T00:00:00"
		]
	]

      But these are Thursdays? I'm a little confused. I would have expected 15th and 22nd Aug 2022 as these are the Mondays of these weeks.

  • Angelina_QBeeQ's avatar
    Angelina_QBeeQ
    ETL
    08-26-2022

    taek_onvi 
    It returns the date of the 4th day of the business week (the middle of the week). And here the case, that Thursday is the middle of the week, which begins on Monday.