Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Issue summing amounts for whole weeks

Hello,

I'm having an issue summing amounts for weeks where the week runs through 2 months.

I'm using set analysis to say sum the amount for the max(month) and max(month)-1 .

Here is an example of what the table looks like:

Week 4/25/2016 - 5/1/20165/2/2016 - 5/8/20165/9/2016 - 5/15/20165/16/2016 - 5/22/20165/23/2016 - 5/29/20165/30/2016 - 6/5/2016
amount155551525145
amount296769646858
Amount1/Amount256%82%74%81%75%78%

   

In this table, I have June selected.  The table should show the amounts for each week of June and May (Data is only pulled through the 5th of June.)

The week of 4/15/16-5/1/2016 only shows the amount for May 1st and not the entire week.

How can I make it so that the expression sums the whole amount for that week even though most of the week is max(month)-2, or April in this case.

Here is how I'm creating the Week field in my script:

WeekStart(DATE1) & ' - ' & WeekEnd(DATE1) As Week,

Here is the set analysis I'm using in the amount fields:

sum({<YearMonth={"$(=max(YearMonth))","$(=date(max(YearMonth)-1,'MMM YY'))" }>}amount1)

)

The result I'm looking for would be this:

Week 4/25/2016 - 5/1/20165/2/2016 - 5/8/20165/9/2016 - 5/15/20165/16/2016 - 5/22/20165/23/2016 - 5/29/20165/30/2016 - 6/5/2016
amount1565551525145
amount2696769646858
amount1/amount281%82%74%81%75%78%

Thank you in advance!

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

Not even sure if this will help us here at all, but I would make my Week field be a date by using dual(). Then weeks sort correctly, you can get the next week by adding 7, and so on.

dual(WeekStart(DATE1) & ' - ' & WeekEnd(DATE1),WeekStart(DATE1)) As Week,

Now on to the actual problem. Well, YearMonth just isn't going to cut it, because to set it to two months excludes the rest of the week. And our week field would be annoying to try to match in set analysis (text match). So let's go by dates. Maybe something like this.

sum({<YearMonth=,Week=,...any other calendar fields...,DATE1={">=$(=date(weekstart(addmonths(max(YearMonth),-1)),'M/D/YYYY'))<=$(=date(weekend(monthend(max(YearMonth))),'M/D/YYYY'))"}>} amount1)

If I wrote that right, it should give you full weeks for both the start of the previous month, and the end of the selected month, even though some days may not be in those months.

View solution in original post

2 Replies
johnw
Champion III
Champion III

Not even sure if this will help us here at all, but I would make my Week field be a date by using dual(). Then weeks sort correctly, you can get the next week by adding 7, and so on.

dual(WeekStart(DATE1) & ' - ' & WeekEnd(DATE1),WeekStart(DATE1)) As Week,

Now on to the actual problem. Well, YearMonth just isn't going to cut it, because to set it to two months excludes the rest of the week. And our week field would be annoying to try to match in set analysis (text match). So let's go by dates. Maybe something like this.

sum({<YearMonth=,Week=,...any other calendar fields...,DATE1={">=$(=date(weekstart(addmonths(max(YearMonth),-1)),'M/D/YYYY'))<=$(=date(weekend(monthend(max(YearMonth))),'M/D/YYYY'))"}>} amount1)

If I wrote that right, it should give you full weeks for both the start of the previous month, and the end of the selected month, even though some days may not be in those months.

Anonymous
Not applicable
Author

This worked!  Thank you so much John!