Set Analysis Filter Based on Variable Table Value

[#1]

Hi,

I need to setup a table that's filtered based values relative to a selected value on a page. For example, there is a table that contains the values 1 - 12. I have a filter setup where the end user can choose a value from that list. Once that value is selected, I have a table that needs to be filtered based on that value. One column is based on that value, the next is the value before the selection in the table, and the next is the value before that.

I need this filter to also be based on the current selections on the page.

I've very new to Set Analysis, and I'm under the gun from the boss to make this happen., so the more detailed explanation you can provide would be appreciated.

Thanks,

Scott

[#2]

Hey John. Thanks for the tip. I checked out the current format of the Day field, and it's M/D/YYY, so I tried the following expression:

Sum({<DAY={'$(=DATE(DAY-1,'M/D/YYYY'))'}>} NUMBER_OF_DOWNLOADS)

Unfortunately, this is still returning a 0. That tells me that the expression is being calculated, but the Set Analysis isn't identifying a Day field that matched Day - 1. If you have any other ideas, I would love to hear them.

Thanks,

Scott

[#3]

I take that back, John. I figured out that the actual format of the data is DD/MM/YYYY, so when I added that to the Set Analysis, it worked great. Thanks a lot for your help.

[#4]

John,

I am doing something similar, but for monthly headcount and attrition analysis. This problem I'm running into is when multiple months are selected. If a single month is selected, your code works fine, but if several months are selected it doesn't. Performance Rating and YearMonth are dimensions in my report and I have an expression that calculates Average Headcount. The requirement is to have a month over month report based on several user selections. Have you come across anything like this? Thanks.

Art

[johnw]

Sets in set analysis are evaluted once for the entire chart, not once per row of the chart. Therefore, set analysis works fine when a single month is selected, but won't work if you have multiple months selected, and want to apply it to each month.

However, I'm unclear on your requirement. What is the definition of "Average Headcount" given a "Performance Rating" and a "Year Month"? It could be as simple as avg(Headcount), but I'm betting there's a lot more to it.

[#6]

The requirement is to let the user select multiple months and dump the data directly to Excel. It is easier for her to do this once instead of 12 times. We would also be using the same logic to produce month over month graphs. I posted this last night:

For a Headcount and Attrition Analysis report, I need to calculate the Average Heacount for any given month as (current headcount + previous headcount)/2. I've created this expression to do that:

(sum({<[Employee_Non-Employee]={'Employee'}>}TurnoverPopulationFlag)) + (sum({1<YearMonth={"$(=(date(AddMonths(YearMonth,-1),'YYYYMM')))"},[Employee_Non-Employee]={'Employee'}>}TurnoverPopulationFlag)))/2

Once I add YearMonth as a Dimension, the above expression no longer works. The previous month's headcount is 0.

[johnw]

OK, so for each month, "Average Headcount" is the headcount for the previous month plus the headcount for the current month, divided by two. You want a user to be able to select, say, a 12 month time frame, and have a chart with all twelve months in it, and the "Average Headcount" for each month, correct?

What I suggest is the following table:

AsOfYearMonth, Type, YearMonth
May 2010, 2mo, May 2010
May 2010, 2mo, Apr 2010
Apr 2010, 2mo, Apr 2010
Apr 2010, 2mo, Mar 2010
etc.

Then your chart would be laid out as follows:

Dimension = AsOfYearMonth
Expression = sum({<[Employee_Non-Employee]={'Employee'},[Type]={'2mo'}>} TurnoverPopulationFlag)/2

And actually, if you don't have other similar requirements with different intervals, you can eliminate the Type field to simplify things. Most examples I see need a type field, so it's kind of habit for me by now, even though your example doesn't need it.

I suspect you could get the same numbers more easily by using the above() function, but I consider chart solutions like that to be less robust than data solutions, as simply changing the sort order, for instance, can break it.

[#8]

Thanks John. I understand what you're doing here, but I'm struggling with how to implement it. Should I do an inline LOAD of the table you described? I'm not really sure how in my Load Script this should be done.

[johnw]

I'd probably do it like this for highest performance:

AsOf:
// Preceeding load to establish the "current month" of the two months you want
LOAD
YearMonth
,YearMonth as AsOfYearMonth
,'2mo' as Type
;
// Directly grab the distict values of YearMonth in your dataset without loading from any tables.
// This is very fast compared to loading distinct values from a large table.
LOAD fieldvalue('YearMonth',iterno()) as AsOfYearMonth
AUTOGENERATE 1
WHILE len(fieldvalue('YearMonth',iterno()))
;
// Concatenate back to itself adding one month to the AsOfYearMonth.
// Preceeding load removes the combination that falls outside of your existing date range.
CONCATENATE (AsOf)
LOAD *
WHERE AsOfYearMonth <= monthstart(today())
;
LOAD
YearMonth
,addmonths(YearMonth,1) as AsOfYearMonth
,Type
RESIDENT AsOf
;

But this would probably be more straightforward and extensible if performance isn't at a premium:

AsOf:
// Load every possible YearMonth from the MainTable.
LOAD DISTINCT YearMonth
RESIDENT MainTable
;
// Do it again with a left join, which will give us every possible combination of two YearMonths.
LEFT JOIN (AsOf)
LOAD YearMonth as AsOfYearMonth
RESIDENT AsOf
;
// Then inner join to eliminate combinations that don't meet our "two month" criterion.
INNER JOIN (AsOf)
LOAD *,'2mo' as Type
RESIDENT AsOf
WHERE YearMonth >= addmonths(AsOfYearMonth,-1)
AND YearMonth <= AsOfYearMonth
;

Both are untested, so I don't guarantee correctness.

[#10]

John,

I used solution #2 and it worked great! Thanks. I missed one thing though. I also have a Voluntary Term Count column. Average Headcount works perfectly. However, my Vol column is now cumulative. How do I display the count just for the month and not the sum for the month and previous month? Is there a function that will pick up the first or last instance?

[johnw]

One approach would be to replace "Voluntary Term Count" with if(YearMonth=AsOfYearMonth,"Voluntary Term Count"). A consistent data approach would be to concatenate a 'Current' Type to our new table for the current month only. Yet another approach would be to replace "Type" with "MonthsBack" like this:

AsOf:
// Load every possible YearMonth from the MainTable.
LOAD DISTINCT YearMonth
RESIDENT MainTable
;
// Do it again with a left join, which will give us every possible combination of two YearMonths.
LEFT JOIN (AsOf)
LOAD YearMonth as AsOfYearMonth
RESIDENT AsOf
;
// Then inner join to eliminate combinations that we don't need.
INNER JOIN (AsOf)
LOAD *,round((AsOfYearMonth-YearMonth)/30.436875) as MonthsBack
RESIDENT AsOf
WHERE YearMonth >= addmonths(AsOfYearMonth,-1)
AND YearMonth <= AsOfYearMonth
;

Then when you want both months, this goes in your set analysis expression:

MonthsBack={0,1}

And when you only want the specific month, you use this instead:

MonthsBack={0}