Set Analysis subtract Dates

[#1]

I have a set analysis expression that I am using which has one condition which requires a subtraction of dates - I am struggling with teh format to insert this.

I have so far

=count({ <Status = {'12-Rcvd'}>} External_HU)

I want to add another condition to this count statement like

([Receive_Dt]-Scheduled_milestone_date)>0 AND ([Receive_Dt]-Scheduled_milestone_date)<=2

Any help appreciated.

Kiran.

[johnw]

Set analysis can only give you values for a specific field, not a difference between two fields. However, as long as we recognize that this:

A - B > C

Is mathematically equivalent to:

A > C + B

Then we have a format that will work with set analysis, at least if we use a search expression in double quotes. I believe the complete expression would be this:

count({<Status={'12-Rcvd'},Receive_Dt={">$(=Scheduled_milestone_date) <=$(=date(Scheduled_milestone_date+2))"}>} External_HU)

Note that this approach REQUIRES that a Scheduled_milestone_date be selected. That may not be what you intended. There are ways around that, but not with set analysis (except for VERY complicated set analysis and other advanced techniques). Instead, I'd handle it with data model changes if, say, Scheduled_milestone_date is a dimension. Let me know if you need that, and I can throw together an example... or better yet, post an example, and I'll throw together a fix.

[#3]

Thanks John,

I appreciate the response. Attached is a test file that I created - It has 2 charts - the top one is what I need numbers and everything - just that I need it restricted to a rolling 3 months automatically. Hence I am converting to use set analysis as with the chart below.

If you could help incorporate teh subtraction in the second chart or even suggest how to achieve this rolling 3 months then it would fir my purpose.

Let me know if any details required.

Cheers.

Kiran.

[johnw]

OK, since you want a three month rolling average for multiple months in a chart, you basically can't use set analysis to do it. A set is only evaluated once for the entire chart, not once per row. There's a complicated way around this limitation, but there's a better solution for this case, which is to handle it with data.

Since your sample file loads from a database, I can't modify and test your script. But you want to generate a table that looks like this:

AsOf:
As Of Received Month, Received Month
Jan 2011, Jan 2011
Jan 2011, Dec 2010
Jan 2011, Nov 2010
Dec 2010, Dec 2010
Dec 2010, Nov 2010
Dec 2010, Oct 2010
Nov 2010, Nov 2010
Nov 2010, Oct 2010
Nov 2010, Sep 2010

Or if you have a lot of different periods you want to use, all based on a number of months, perhaps generate it for ALL months back to the beginning of your data, and include a Months Back field:

AsOf:
As Of Received Month, Months Back, Received Month
Jan 2011, 0, Jan 2011
Jan 2011, 1, Dec 2010
Jan 2011, 2, Nov 2010
...
Jan 2011, 48, Jan 2007
...

For both types of tables, you'd use As Of Received Month instead of Received Month in your chart. For the first type of table, you just use the same expression as you would otherwise use. So if you want to sum sales for a rolling three months, you'd just use sum(Sales). For the second type of table, you have to specify how many months back you want. So for a rolling three months of sales, you'd use sum({<"Months Back"={">=0 <=2"}>} Sales). So the first is simpler, but the second gives you more chart flexibility.

A THIRD approach, useful when you want multiple periods in the same chart, is to use an Accumulation Type field instead of Months Back, with values like 'This Month', 'Previous Month', '3 Month Rolling', 'YTD', '12 Month Rolling' and so on. Then make a pivot table with Accumulation Type as a dimension, and move it to the top. Now a simple sum(Sales) will give you all of those different types of accumulation in the same chart.

If that makes sense to you, but you need help with the script to efficiently generate one of these kinds of tables, let me know.

[#5]

John,

The above are very good solutions for range setup.

1> The database option I like because of the simplicity - I will have to create a procedure that assigns a As of Month to every coming month within the data

2> The second solution I do not know how to handle future months in the data - Would I always reassign 0 to current month and move backwards.

I am doing a summary chart based on 4 different date fields (Receive date being one of them) - the set analysis functionality for a rolling 3 months works for my first 3 summaries since they do not have a subtraction of dates about them.

eg. This one does a rolling 3 months for me.

= count({$<Loaded_Shipment_Dt={">=$(=Timestamp(MonthStart(AddMonths(Today(), -3)), 'DD/MM/YYYY h:mm:ss[.fff] TT'))<$(=Timestamp(MonthStart(Today()), 'DD/MM/YYYY h:mm:ss[.fff] TT'))"}, Time_To_Load={"<=5"}, Status={'22-Loaded'}>}distinct External_HU)

Is my expression going to be wrong based on your comment "A set is only evaluated once for the entire chart, not once per row. "Appreciate the efforts and I will start walking the first option instead.

[johnw · Accepted solution]

For #2, you've misunderstood what I was doing, and looking back at my example, it's easy to see why. Here's the example extended to include future months. Hopefully it's a little more clear with more of the table filled in:

AsOf:
As Of Received Month, Months Back, Received Month
Apr 2011, 0, Apr 2011
Apr 2011, 1, Mar 2011
Apr 2011, 2, Feb 2011
...
Apr 2011, 51, Jan 2007
Mar 2011, 0, Mar 2011
Mar 2011, 1, Feb 2011
Mar 2011, 2, Jan 2011
...
Mar 2011, 50, Jan 2007
Feb 2011, 0, Feb 2011
Feb 2011, 1, Jan 2011
Feb 2011, 2, Dec 2010
...
Feb 2011, 49, Jan 2007
Jan 2011, 0, Jan 2011
Jan 2011, 1, Dec 2010
Jan 2011, 2, Nov 2010
...
Jan 2011, 48, Jan 2007
...

For this:

= count({$<Loaded_Shipment_Dt={">=$(=Timestamp(MonthStart(AddMonths(Today(), -3)), 'DD/MM/YYYY h:mm:ss[.fff] TT'))<$(=Timestamp(MonthStart(Today()), 'DD/MM/YYYY h:mm:ss[.fff] TT'))"}, Time_To_Load={"<=5"}, Status={'22-Loaded'}>}distinct External_HU)

It works fine because the date range is based on today(). Where you get into trouble and need a data solution is if you want something like this:

Month Rolling 3 Months Sales
Jan 2011 $500
Feb 2011 $600
Mar 2011 $550

Here, you might think you could refer to the "Month" field in your rolling three months set analysis expression. But since the set is only evaluated once for the entire chart, "Month" has multiple values, so will return null() in your expression, and the whole expression will fail.

[#7]

Excellent - Many thanks.