Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
wttaryde
Contributor III
Contributor III

building Filter into If statement

In my expression below, Line 1 needs to filter for the answer to a specific question (IT0060_O_1)  If I remove line 1 and just filter ALL of my data to that question then the rest of the statement works.  But I don't want to filter all of my data and I can't use alternate states to just filter this one graph.  So I need the filter to be part of the expression. I've tried many ways and can't find the right syntax.

If(wildmatch(QPermID,'IT0060_O_1*'),
if(Response like '*Oracle*','#0033ff',
if(Response like '*SAP*','#FCC000',
if(Response like '*Cayenta*','#FC0000',
if(Response like '*Accenture*','#00C000',
if(Response like '*In-House*','#777777','#000000'))))))

 

Thanks

1 Solution

Accepted Solutions
wttaryde
Contributor III
Contributor III
Author

I have taken a different approach.  I was trying to color code the bar chart.  instead I've created a stacked bar with each piece being given different criteria in the expression.  By changing the criteria in line 2 each bar in my chart  is a different color. Since the alternative is Null, no one is actually stacked.

=(Avg({1<Year = {$(=Max(num#(Year ,'####')))}>} Aggr(
if(only({1<QPermID = {"IT0060_O_1_*"}>}Response) like '*SAP*',
max({1<QPermID={"IT0070_R_1_*"}>}Response)
,null())
,YearID,Year)))

 

wttaryde_0-1625091726426.png

 

View solution in original post

5 Replies
sasikanth
Master
Master

Try below one,

 

If( Wildmatch(QPermID,'IT0060_O_1*'),

pick(wildmatch(Response , '*Oracle*', '*SAP*', '*Cayenta*', '*Accenture*', '*In-House*')+1, '#000000',

'#0033ff', '#FCC000', '#FC0000', '#00C000', '#777777'))

wttaryde
Contributor III
Contributor III
Author

Your expression didn't change the top line which is the one not working.  Both expressions will color the graph correctly when a filter is applied, but neither does it with just the Wildmatch(QPermID, ...) embedded

sasikanth
Master
Master

could you explain your complete requirement??

above expression filters QPermID`s start with ,'IT0060_O_1*' and corresponding Responses, 

second expression decode values as specified

 

**example below filters all employees related to Central Staff  department and then decodes as specified. 

=if(WildMatch([Dept Name],'Central Staff*'),pick( WildMatch([EMP Name],'Jonna*','Laila*')+1,'ELSEPART','LAILA','JONNA')
)

wttaryde
Contributor III
Contributor III
Author

I've included an app so you can see the expression isn't doing what I need.  I have set the default bookmark and when the app is filtered the graph looks as desired.  But without the filter, the expression doesn't produce the desired results.  I appreciate the help.

wttaryde
Contributor III
Contributor III
Author

I have taken a different approach.  I was trying to color code the bar chart.  instead I've created a stacked bar with each piece being given different criteria in the expression.  By changing the criteria in line 2 each bar in my chart  is a different color. Since the alternative is Null, no one is actually stacked.

=(Avg({1<Year = {$(=Max(num#(Year ,'####')))}>} Aggr(
if(only({1<QPermID = {"IT0060_O_1_*"}>}Response) like '*SAP*',
max({1<QPermID={"IT0070_R_1_*"}>}Response)
,null())
,YearID,Year)))

 

wttaryde_0-1625091726426.png

 

View solution in original post