0 Replies Latest reply: Apr 8, 2013 10:17 AM by raksh208 RSS

    Set analysis for alternate state and default state in single expression.



      Can we write set analysis expression  to handle selection made on both default state as well as alternate state?


      I have 2 list boxes. Period1 is in default state and Period2 in alternate state.


      A pivot chart has 2 set analysis expressions (one for period1 and other for period2). So, when selection is made in Period1 list box, only the first column of the pivot table should change. If Period2 is selected, only 2nd column having expression for period2 should change.


      But, since period1 is in default state, it is affecting the 2nd column in the chart. How do I avoid it? I have specified the scope in set analysis expression but it doesn't seem to work according to the requirement.


      Expression for Period1 in pivot table:


      If(count distinct Period1)=1, sum({< Month=, Year=, [Month Year] = $::[Period1]>}Amount),sum({<Year=, Month=, [Month Year]={'$(vCurrentMonth)'}>}Amount))


      Expression for Period2 in pivot table(period2 is in alternate state):


      If(count( {Period2} distinct Period2)=1, sum( {Period2 <Month=, Year=, [Month Year]= Period2 >} Amount) , sum( {Period2 <[Month Year]= {'$(vPreviousMonth)'}>} Amount)


      If no selections are made on 2 list boxes, 1st column in pivot table should display current month(March 2013 data) and 2nd column should display previous month data(Feb 2013).

      The issue is, if Dec 2012 is selected from period1 list box, the chart should display Dec 2012 value in 1st column and 2nd column should be unaffected.(it should still show Feb 2013 data). But because of the else condition in expression 2, the chart displays previous month data according to selections made in Period1 list box, which is Nov 2012.


      Note: The pivot table is in inherited state. Alternate state is applied only on Period2 list box and set analysis expression in pivot table.


      How should I modify expression to get values based on both default and alternate state selection?


      Thanks !