Set Analysis Basics

[avastani]

Hi,

Here's a scenario. Listbox for Year and Quarter. User can pick either or both.

Straight Table chart expression is to display Sum(Sales) for Prior Quarter End.

When the user picks only a Year from the listbox, the expression displays perfectly. However, when the user also picks a Quarter, the expression goes blank.

Is this traditional set analysis? I thought no matter what, it would always return the value I want specified in the set analysis portion. Guess it isn't true when you have an implicit {$} and time series in the set analysis.

Do I need to do a SUM({1<Date={$(=AddMonths(QuarterEnd(Date), -3))}>}Sales) i.e. add a {1} in the set? My dimension is not time series but CategoryName.

I think I have my basics mixed up. Any help appreciated.

[pover]

Your set analysis control the Date field, but the {$} respects the Quarter listbox selection.  If the set analysis calls on a Date that is not in the Quarter selected no value will be shown.  In the set analysis you should include the fields that can directly affect your set analysis (Usually these are fields that are within the same hierarchy of the field you have in your set analysis.)  For example,

sum({$<Quarter=, Date={$(=addmonths(quarterend(max(Date)),-3))} Sales)

Also, you should use some aggregation function, like max(), to only return one value.  The max(Date) will respect the Quarter that is selected.

Karl