Report with week ending

I have a report which is run on a monthly basis. The report basically takes a sum of quantity sold on a daily basis.

It comes like ITEM DATE SUM

1 2019-09-01 10

1 2019-09-02 20

2 2019-09-02 10

What i need to do is take the week end which will be like 2019-09-07 and then the total sum. I have got the week number calculation part done. How to get the report in format

2019-09-07 1 30

2019-09-07 2 10

Developer Tools

Preparation

Optimization

Accepted answers

danilang

HI @rajatjain1

@PhilipMannering's solution was close but gave the date of Sunday as the week end instead of Saturday

Change his formula to

if DateTimeFormat(date,'%u') = "7" then
	datetimeadd(Date, 6, 'days')	
else
	datetimeadd(Date, 6-tonumber(DateTimeFormat(date,'%u')), 'days')
endif

to get results with Saturday's date

Dan

WeekEnding.yxmd

All comments

JoshuaGostick

Hi @rajatjain1,

If you already have a field with the week end, you can just use the Summarize tool to get your desired output.

In the Summarize tool, group by Item, group by the week end field, and Sum the sum field.

Let me know if you have any questions.

philipmannering

You could try something like this,

datetimeadd(Date, 7-tonumber(DateTimeFormat(date,'%u')), 'days')

Week Ending.PNG

Week Ending.yxmd

danilang

HI @rajatjain1

@PhilipMannering's solution was close but gave the date of Sunday as the week end instead of Saturday

Change his formula to

if DateTimeFormat(date,'%u') = "7" then
	datetimeadd(Date, 6, 'days')	
else
	datetimeadd(Date, 6-tonumber(DateTimeFormat(date,'%u')), 'days')
endif

to get results with Saturday's date

Dan

WeekEnding.yxmd

philipmannering

@danilang I adhere to the ISO 8601 where Sunday is end of the week.

danilang

I see, @PhilipMannering

We here in the Great White North along with our American brethren to the immediate south and our Aussie cousins half way around the world are still bucking the "week ends on Sunday" trend

Dan

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335