11 Replies Latest reply on Feb 26, 2018 7:07 AM by Shinichiro Murakami

# Running total line showing comparison between 'week year' with 'week year -1'

My goal is to compare application volume between 2 academic years on a weekly basis.

The final outcome will look like Picture 1. 2017-2018's application volume data is represented in the orange line and 2018-2019's in the blue line.

The challenge is that the year has 53 weeks, but the window to receive applications is 59 weeks. For example:

2017-2018 applications can be submitted from December 2016 to May 2018

2018-2019 applications can be submitted from December 2017 to May 2019

So year must be included in the x-axis (refer to Picture 2), but now we have these issues:

-The line breaks between years

-We lose the comparison on the same vertical line between week numbers shown on Picture 1.

I am thinking that the solution needs to include a calculation that will say bring together Week of Current year and Week of Last Year. Not sure how to develop this, but this table will give you another idea what we need to compare:

 2016 W46 2016 W47 2016 W48 2016 W49 2016 W50 2016 W51 2016 W52 2016 W53 2017 W1 2017 W2 2017 W3 2017 W4 2017 W5 2017 W46 2017 W47 2017 W48 2017 W49 2017 W50 2017 W51 2017 W52 2017 W53 2018 W1 2018 W2 2018 W3 2018 W4 2018 W5

Picture 1:

Picture 2:

Thank you!

• ###### 1. Re: Running total line showing comparison between 'week year' with 'week year -1'

Hi Diogo,

I am not sure about your week's logic, but you can modify the datetrunc formula based on your starting day of week.

(Assuming Sunday at this point)

\

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: Running total line showing comparison between 'week year' with 'week year -1'

My week logic is the official week number. As shown in this table below.

On my sample, schools X and Y started accepting applications in December, while the other schools in January. It's important to show December's weekly application volume for Week 50, 51, 52, etc. And January's Week 1, 2, 3...

I like your suggestions a lot. Maybe I can use it and accomplish something to denote the actual week number with labeling, but that might get confusing.

Let me know if you can think of anything else. Once again, thanks a lot.

• ###### 3. Re: Running total line showing comparison between 'week year' with 'week year -1'

HI Diogo,

Minor formula modification might be needed, but key here is you can sort the weeks label based on another calculated field.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 4. Re: Running total line showing comparison between 'week year' with 'week year -1'

Pure magic! You are the man.

• ###### 5. Re: Running total line showing comparison between 'week year' with 'week year -1'

HI Diogo,

if the field is numeric, sum or average is the typical aggregation, but you can use "min" and "max" to get one vale from multiple "dimensions"' value.

Especially in the case dimension is  "Date", "min" brings earliest date and "max" brings the latest date.

Sometimes/quite often, only for the purpose to align aggregation level, you need to add min, max on dimension side as well, not only measure fields.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 6. Re: Running total line showing comparison between 'week year' with 'week year -1'

Shinichiro Murakami I trying to make sense of your calculations to learn. And I observed something interesting that I'd appreciate your opinion.

What's the risk in adding [School Code] to the Base Date LOD? When I added it, the Week 53 and Week 1 get sorted properly. Let me know what you think.

• ###### 7. Re: Running total line showing comparison between 'week year' with 'week year -1'

In general,

You should not add "school" in LOD to calculate min date UNLESS the calendar is different from school.

If each school has different calendar, it make sense.

Thanks,

Shin.

• ###### 8. Re: Running total line showing comparison between 'week year' with 'week year -1'

Schools have a different application window. Some open the application window in early December, while other open in early January. Would this qualify as 'different calendar' per school?

• ###### 9. Re: Running total line showing comparison between 'week year' with 'week year -1'

I think so.

Shin

• ###### 10. Re: Running total line showing comparison between 'week year' with 'week year -1'

Can you help me understand the role of 1 in this formula?

int(([Apptime]+1-[Base Date])/7)

Thank you!

• ###### 11. Re: Running total line showing comparison between 'week year' with 'week year -1'

Oh, might be typo.

int(([Apptime]-[Base Date])/7)+1