Set Analysis using Data Island

[#1]

Hi

I am trying to using some complex set analysis in one of my formula.  I had originally performed this function in the script, however our users need to be able to adjust the month so precalculating is no longer an option.

What I am trying to achieve is offset budget.  Thus, should a particular project have not started yet, despite being budgeted to start in April say, I want April's budget to show as next months budget (according to the users selection),  Therefore, should they select October 2013, then November's budget amount should present April's Budget, December should show May's budget etc. The set analysis then aggregates this by Financial Year.  Thus, the Planned Project Start Date (GST_Months) - The selected date (SelDate_Months) should and does equate to the number of months I need to slip the budgeted amount.

Financial Year is the financial year of the budgeted amount.  GST_Months is the planned start date of the project.  SelDate_Months is the field from the data island that drives the selection.

[Financial Year] = {$(#=Only(Year(AddMonths(EndofMonth,(GST_Months - SelDate_Months)))))}

This formula works in the following situations;

1) When SelDate_Months is removed.

2) When GST_MOnths is removed.

3) When I select only one project to look at.

However, when I remove the project selection to look at multiple selections the information disappears and is replaced with 0's.

Can anyone shed any light on why I am so close but simply showing more than one project in a straight table results in a 0 being returned?

Many thanks

Steve

[#2]

Hi Steve,

I think the 'only' function requires a selection to work properly, hence the 0's your seeing without any selections.

I use data islands in some of my docs an example posted here:

Data Islands and Set Analysis

Perhaps you'll find it helpful. Instead of using 'only', I'm nesting conditional statements in the set analysis and it's working well, although it's quite slow.

Hope that helps.

Matt

[#3]

Hi Matthew

Thank you for your reply.  I have tried the only (as well as min, max, etc) on the formula, in fact I have one already in the proposition as it is.  When I introduce 'only' either individually to the fields or to the result of the formula, then the set analysis stops working when I have just one project selected.

Any additional suggestions would be welcome.  Also, if it helps, my formula in full is;

Sum({$< [Financial Year] = {$(#=Only(Year(AddMonths(EndofMonth,0+ (GST_Months - SelDate_Months)))))},

[Phase Status] -= {'Withdrawn'}, [Phase Type ID] = {'2'}, [Closed Date] = {">=$(=max(Num([EndofMonth])))"}   >}

[Budget] * $(CreditBasis))

/$(ScalingFactor)

Thanks

Steve

[swuehl]

Steve,

the set expression is evaluated once per chart, not per dimension value.

So if you expect your set expression to take care of your current dimension line value, it just won't do that.

Also, if you use a dollar sign expansion in your field modifier, it is evaluated to the text replacement in the total context.

=Only(Year(AddMonths(EndofMonth,0+ (GST_Months - SelDate_Months))))

should be evaluated per project, right? But how should QV know that, i.e. how should QV return a single value when there maybe multiple answers for different projects?

If you select one single project, this ambiguity is resolved.

So a possible solution might involve that you are not able to use set analysis. But to answer this, you should post more details, best by posting a small sample app.

[#5]

Hi

Thanks for your answer.  That makes sense, one answer per project.  However, there is only one answer per project and I am including the grant in the straight table - I included a count expression to ensure only one record of GST_Months and SelDates_Months was coming through (which is was) and still nothing was returned.

But, does the system know this when calculating the set analysis?  I thought yes, as when I replaced GST_Months - SelDate_Months with GST_Months - GST_Months is evaluated correctly.  However, when I introduce the actual value of GST_Months, i.e. GST_Months - 24199, then it does struggle again.

The model is very large and very complex so it would be quite a task to provide an example while protecting my data.

Can you suggest an alternative method for approaching this question please?

Thanks

Steve