11 Replies Latest reply on Aug 10, 2016 5:10 PM by Shinichiro Murakami

# Group Data my Week

Hi

Please find the attached document , I am trying to group data by Week , monthly and by day seems to give me right results but not Week created bugs

What is the formula I have to use to get data aggregated by week

Thanks!

• ###### 1. Re: Group Data my Week

Hi, Sandy

Could you explain little bit more in detail?

Which calculated field has problem, which sheet has problem, something like that?

Thanks,

Shin

• ###### 2. Re: Group Data my Week

Please take a look at the Created Bugs charts .. there are 3 one is with Month , Day and Week .. the counts of Month and Day match for example if you look for August 602 , but for weekly data August shows wrong count .. I wanted to know how to show 602 for August Week ... basically want to aggregate data by Weeks

• ###### 3. Re: Group Data my Week

Still not enough where is 602   /   which "Create Bug chart"? could you please illustrate something?

Thanks,

Shin

• ###### 4. Re: Group Data my Week

It is the first Tab

• ###### 6. Re: Group Data my Week

Thi is the data background.

Monthly

Weekly

Display shows 08/07/16 in weekly granularity.

This is actually 08/07/16 ~ 08/13/2016 and other August date of 08/01/2016 ~ 08/06/2016 is counted in "07/31/2016"

Now I need to understand what you exactly need for weekly calc..

Thanks,

Shin

• ###### 7. Re: Group Data my Week

What should I do to show it Weekly ? I mean aggregated by Week ... sum of all days from 8/1 - 8/7 displayed for 8/7 as a row, it is quite confusing to show date 7/31 but count 8/1 - 8/7

• ###### 8. Re: Group Data my Week

Sandy,

I'm still not sure which weekly calc you want.

Are you asking like all week starting from Sunday(in this case) and cut at the end of month?

Based on this logic, 7/31~7/31 is the last week of July and 8/1~8/6 is the first week of Aug.

Based on your comment, it looks like you want to sum 8/1~8/7.

Does this mean you want sum 1~7, 8~14, 15~21, 23~28, and 29-end  for each month?

Thanks,

Shin

• ###### 10. Re: Group Data my Week

What Yes means?

I'm asking which option you want.

1:   7/31~7/31 is the last week of July and 8/1~8/6 is the first week of Aug.

2:  sum 1~7, 8~14, 15~21, 22~28, and 29-end  for each month

Shin

• ###### 11. Re: Group Data my Week

I'm not sure you are still interested in the solution or not, but anyways,

We have at least 4 option for 'week' display

[Option 1. 2]  x  [show start date / end date]

And each formula is shown as below.

[Date Option 1(start)]

date(

max(

max(Datetrunc('month',[Jira Create Ts]),datetrunc('week',[Jira Create Ts]))

,

)

)

[Date Option 1 (end)]

date(

min(

datetrunc('week',[Jira Create Ts])+6))

[Date Option 2(start)]

date(

str(YEAR([Jira Create Ts]))+","+

str(month([Jira Create Ts]))+","+

str((int((day([Jira Create Ts])-1)/7)*7)+1))

[Date Option 2 (end) ]

min(

date(

str(YEAR([Jira Create Ts]))+","+

str(month([Jira Create Ts]))+","+

str((int((day([Jira Create Ts])-1)/7)*7)+1))+6

,