Set analysis with selected values

[javier_florian]

Hi All,

I have three expression with set analysis to calculate a sales in month selected, budget in month selected and budget in a previous year for month selected, my formula to last expression is:

Sum({1<Field_1={'Criteria'}, YearMonth={$(vPrevMaxYearMonth)}>} Value)

"({1<" take all records...

However, when my user select a product, my set expression doesn't work because pivot table shows all products.

How can fix or improve my set expression if user needs select a year-month, product, business line, and another dimensions?

Thanks in advanced.

[swuehl]

If you want the set expression to be sensitive to user selections, just overriding selections in fields Field_1 and YearMonth, try a set identifier $:

Sum({$<Field_1={'Criteria'}, YearMonth={$(vPrevMaxYearMonth)}>} Value)

[javier_florian]

Hi Swuehl,

When I use that formula, pivot table show:

My formulas are:

Sales 2014-Apr -> =-(Sum({$<Field_1={'Criteria'}, YearMonth={$(vMaxYearMonth)}>} (Value)))

Budget 2014-Apr -> =-(Sum({$<Field_1={'Criteria'}, YearMonth={$(vMaxYearMonth)}>} (Budget)))

Sales 2013-Apr -> =-Sum({1<Field_1={'Criteria'}, YearMonth={$(vPrevMaxYearMonth)}>} Value)

Regards,

-JFlorian

[swuehl]

Any reason why you still keep the set identifer 1 in the last expression for Sales 2013-Apr?

[javier_florian]

If keep formula:

-Sum({$<Field_1={'Criteria'}, YearMonth={$(vPrevMaxYearMonth)}>} Value)

And user select a year-month, I can get any value (result -> 0)

[swuehl]

Ok, if you want to ignore the user's selection in all date fields, like Year and Month, it's not enough to override just YearMonth, you would need to explicitely ignore all the other date fields (assuming Date, Month, Year for now) the user may select in when using the default set identifier:

Sum({$<Field_1={'Criteria'}, YearMonth={$(vPrevMaxYearMonth)}, Date=, Month= , Year= >} Value)

If you only want to regard few fields the user may select, like Product, you can use set identfier 1 and pulling the users selection in:

Sum({1<Field_1={'Criteria'}, YearMonth={$(vPrevMaxYearMonth)}, Product = $::Product>} Value)

or maybe

Sum({1<Field_1={'Criteria'}, YearMonth={$(vPrevMaxYearMonth)}, Product = p() >} Value)

There are differences in last two expressions in the way possible values for field Product (white elements in list box) are handled.