Dynamic Expression

[Anonymous]

I am trying to write and expression in a straight table that will alternate between Year, Month, Quarter per selectable filters as well as differentiate between "Booked" and "Shipped" orders based on a toggle.

The below expressions is working and updating the measure dynamically but it is does not include Booked Month orders.

=if(GetSelectedCount([Fiscal - Year])>0,

if($(Surgical_Amount_Type)='Booked Amount',

if(GetSelectedCount([Fiscal - Year])>0,

$(aYearBookedToDate))

,$(aYearShippedToDate))

,

if(GetSelectedCount([Fiscal - Year Quarter Name Long])>0,

if($(Surgical_Amount_Type)='Booked Amount',

if(GetSelectedCount([Fiscal - Year Quarter Name Long])>0,

$(aQuarterBookedToDate))

,$(aQuarterShippedToDate))

,

if(

GetSelectedCount([Fiscal - Period Name Year Short])>0,

$(aMonthShippedToDate))))

When I attempt to update the expression to include the Booked Orders variable, the whole expression breaks. Im not sure what I am doing wrong as I have moved the open parenthesis countless times. Below is the piece I added in red. 

=if(GetSelectedCount([Fiscal - Year])>0,

if($(Surgical_Amount_Type)='Booked Amount',

if(GetSelectedCount([Fiscal - Year])>0,

$(aYearBookedToDate))

,$(aYearShippedToDate))

,

if(GetSelectedCount([Fiscal - Year Quarter Name Long])>0,

if($(Surgical_Amount_Type)='Booked Amount',

if(GetSelectedCount([Fiscal - Year Quarter Name Long])>0,

$(aQuarterBookedToDate))

,$(aQuarterShippedToDate)))

,

if(GetSelectedCount([Fiscal - Period Name Year Short])>0,

if($(Surgical_Amount_Type)='Booked Amount',

if(GetSelectedCount([Fiscal - Period Name Year Short])>0,

$(aMonthBookedToDate))

,$(aMonthShippedToDate))))

Any ideas?

[Digvijay_Singh]

Not sure but may be this -

=if(GetSelectedCount([Fiscal - Year])>0,

if($(Surgical_Amount_Type)='Booked Amount',$(aYearBookedToDate),$(aYearShippedToDate))

,

if(GetSelectedCount([Fiscal - Year Quarter Name Long])>0,

if($(Surgical_Amount_Type)='Booked Amount',if(GetSelectedCount([Fiscal - Year Quarter Name Long])>0,$(aQuarterBookedToDate),$(aQuarterShippedToDate)))

,

if(GetSelectedCount([Fiscal - Period Name Year Short])>0,

if($(Surgical_Amount_Type)='Booked Amount',if(GetSelectedCount([Fiscal - Period Name Year Short])>0,$(aMonthBookedToDate),$(aMonthShippedToDate))))))

[stevedark]

The code in red has an unbalanced number of parentheses, there are five opening parentheses and only four closing.  You may need another closing one at the end of that section?

Also, what is the content of the Surgical_Amount_Type variable?  If it is a fixed value (rather than code) then you may need to include that in single quote marks, i.e.:

if('$(Surgical_Amount_Type)'='Booked Amount',

Hope that points you in the right direction,

Steve

[sasiparupudi1 · Accepted solution]

May be try

if(GetSelectedCount([Fiscal - Period Name Year Short])>0,

if($(Surgical_Amount_Type)='Booked Amount',

if(GetSelectedCount([Fiscal - Period Name Year Short])>0,

$(aMonthBookedToDate)))

,$(aMonthShippedToDate))))