Issue summing amounts for whole weeks

[Anonymous]

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!

[johnw · Accepted solution]

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]

This worked!  Thank you so much John!