Set analysis with combination of current selection and total

[#1]

Hello,

I would appreciate any help with the following issue.

(1) I have a sheet where I have selected TWO dimensions (a) CurrentYear and (b) PersonName

(2) I cannot deselect the CurrentYear as it is used to limit values (expressions) in other charts

(3) I want to sum the SalesAmt field, by (a) CurrentYear and (b) PersonName

The PersonName is therefore based on the current selection ($ in set analysis) but the CurrentYear is based on the full data set (Total in set analysis).

If I use $1 then the result only works if I first select PersonName and then CurrentYear. If I select CurrentYear first and the PersonName it does not work.

Thanks in advance,

Craig

[Anonymous]

Did you try to use {1} in your expression to get the full data set?

sum( {$} Sales )
returns sales for the current selection, i.e. the same as sum(Sales).

sum( {1} Sales )
returns total sales within the application, disregarding the selection but not the dimension. If used in a chart with e.g. Products as dimension, each product will get a different value.

sum( {1} Total Sales )
returns total sales within the application, disregarding both selection and dimension. I.e. the same as sum(All Sales).

Hope this is helpfull.

Dennis.

[#3]

You can use the $ for current selections but then remove any selection for CurrentYear e.g.

sum({$<CurrentYear=>} SalesAmt)

Regards,

Gordon

[#4]

Hi Dennis & Gordon,

Thanks for your suggestions. I have tried a few options including the following syntax:

=count({total <CurrentYear=,PersonName=PersonName>} SalesDate)

The following occurs when I select values for PersonName and CurrentYear:

(a) On selecting a PersonName the results are correct - totals are shown by year for selected person

(b) On select the CurrentYear the results are incorrect - totals are only shown for the selected year which is 2011

Also, if I select CurrentYear first and then PersonName, the results are as per (b) above.

If I change the statement to specify a range of CurrentYear (per below) then this only works if I do not select a CurrentYear for the dimension.

=count({total <CurrentYear={'2011','2010','2009'},PersonName=PersonName>} SalesDate)

I may well be mixing up my syntax here but I would expect the above statement to always look at the full data set and cannot understand why the CurrentYear is not being ignored.

I would appreciate any further suggestions on this one.

Thanks again,

Craig

[#5 · Accepted solution]

Hi,

The following code, where I substituted CurrentYear with SalesDate, has resolved the problem and works regardless of the order in which I select PersonName or CurrentYear.

=count({total <SalesDate=, PersonName=PersonName>} SalesDate)

The reason it works is that the values in the CurrentYear list was being derived as Year(SalesDate) instead of using the derived field (built at load time) called CurrentYear. The actual underlying field is therefore SalesDate and not CurrentYear.

Regards,

Craig