Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
cancel
Showing results 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/2016 5/2/2016 - 5/8/2016 5/9/2016 - 5/15/2016 5/16/2016 - 5/22/2016 5/23/2016 - 5/29/2016 5/30/2016 - 6/5/2016 amount1 5 55 51 52 51 45 amount2 9 67 69 64 68 58 Amount1/Amount2 56% 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/2016 5/2/2016 - 5/8/2016 5/9/2016 - 5/15/2016 5/16/2016 - 5/22/2016 5/23/2016 - 5/29/2016 5/30/2016 - 6/5/2016 amount1 56 55 51 52 51 45 amount2 69 67 69 64 68 58 amount1/amount2 81% 82% 74% 81% 75% 78%

Thank you in advance!

1 Solution

Accepted Solutions
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.

2 Replies
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!

Community Browser