Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
mikegrattan
Specialist
Specialist

Set expressions working correctly, but give "Error in expression" when included in an If statement

Here is the entire expression:

=If((vMeasure) = 'EquivPrice',
(Num(Sum({<ShipDate_DayOfWeek={'Mon'},Week={'$(vWeek)'},ShipYear={'$(=$(vYear))'}>}ExtendedPrice) / Sum({<ShipDate_DayOfWeek={'Mon'},Week={'$(vWeek)'},ShipYear={'$(=$(vYear))'}>}EquivalentUnits),'$##.00'))
,
If((vMeasure) = 'CartonPrice',
(Num(Sum({<ShipDate_DayOfWeek={'Mon'},Week={'$(vWeek)'},ShipYear={'$(=$(vYear))'}>}ExtendedPrice) / Sum({<ShipDate_DayOfWeek={'Mon'},Week={'$(vWeek)'},ShipYear={'$(=$(vYear))'}>}CartonEquiv),'$##.00')),
(Num(Sum({<ShipDate_DayOfWeek={'Mon'},Week={'$(vWeek)'},ShipYear={'$(=$(vYear))'}>}$(vMeasure)),'#,##0'))
)
)

Here is how it evaluates in the error panel:

mikegrattan_0-1737569610067.png

 

 

It appears that all variables are evaluated correctly, and when I get out of the Edit mode, the results look good in the table, even after changing multiple variables to other values. 

Is there a way to correct this?

 

Labels (2)
1 Solution

Accepted Solutions
mikegrattan
Specialist
Specialist
Author

Updated it as follows and now it works:

 

 

=If((vMeasure) = 'EquivPrice',
(Num(Sum({<ShipDate_DayOfWeek={'Mon'},Week={'$(vWeek)'},ShipYear={'$(vYear)'}>}ExtendedPrice) / Sum({<ShipDate_DayOfWeek={'Mon'},Week={'$(vWeek)'},ShipYear={'$(vYear)'}>}EquivalentUnits),'$##.00'))
,
If((vMeasure) = 'CartonPrice',
(Num(Sum({<ShipDate_DayOfWeek={'Mon'},Week={'$(vWeek)'},ShipYear={'$(vYear)'}>}ExtendedPrice) / Sum({<ShipDate_DayOfWeek={'Mon'},Week={'$(vWeek)'},ShipYear={'$(vYear)'}>}CartonEquiv),'$##.00')),
(Num(Sum({<ShipDate_DayOfWeek={'Mon'},[Week]={'$(vWeek)'},ShipYear={'$(vYear)'}>}$(vMeasure)),'#,##0'))
)
)

View solution in original post

1 Reply
mikegrattan
Specialist
Specialist
Author

Updated it as follows and now it works:

 

 

=If((vMeasure) = 'EquivPrice',
(Num(Sum({<ShipDate_DayOfWeek={'Mon'},Week={'$(vWeek)'},ShipYear={'$(vYear)'}>}ExtendedPrice) / Sum({<ShipDate_DayOfWeek={'Mon'},Week={'$(vWeek)'},ShipYear={'$(vYear)'}>}EquivalentUnits),'$##.00'))
,
If((vMeasure) = 'CartonPrice',
(Num(Sum({<ShipDate_DayOfWeek={'Mon'},Week={'$(vWeek)'},ShipYear={'$(vYear)'}>}ExtendedPrice) / Sum({<ShipDate_DayOfWeek={'Mon'},Week={'$(vWeek)'},ShipYear={'$(vYear)'}>}CartonEquiv),'$##.00')),
(Num(Sum({<ShipDate_DayOfWeek={'Mon'},[Week]={'$(vWeek)'},ShipYear={'$(vYear)'}>}$(vMeasure)),'#,##0'))
)
)