Report with week ending

[rajatjain1]

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

[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')

 

[danilang · Accepted solution]

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

[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