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

Announcements
Discover how organizations are unlocking new revenue streams: Watch 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'))
)
)