cancel
Showing results for 
Search instead for 
Did you mean: 

Week to date as JAQL query

taek_onvi
8 - Cloud Apps
8 - Cloud Apps

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
			}
		}
	}		
]
}

 

 

 

3 REPLIES 3

Angelina
9 - Travel Pro
9 - Travel Pro

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

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
9 - Travel Pro
9 - Travel Pro

@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.