Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

danielco223
New Contributor III

Dynamic Expression

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?

1 Solution

Accepted Solutions
sasiparupudi1
Honored Contributor III

Re: Dynamic Expression

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))))

3 Replies
Digvijay_Singh
Honored Contributor III

Re: Dynamic Expression

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))))))

MVP
MVP

Re: Dynamic Expression

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
Honored Contributor III

Re: Dynamic Expression

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))))

Community Browser