Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jcampbell474
Creator III
Creator III

Help with Nested If Expression

I feel like this is one of those moments where it's something simple and I just can't see it. Hoping someone can tell me what I'm doing wrong here.

I have a few charts to show Top & Bottom 5, depending on the values of two variables.  All charts work, except for one.  The 1st two If's are the ones I need in this chart and they work if I just use them, or if I just use them and add If(1=1,'Yes'))) at the end.  In other words, the expression stops at the 2nd row.  But, when the rest of the nested If's are used, it says that there is no data to be displayed.  Seems like it doesn't stop at the 2nd row.

Here are the variables:

vCallsExpressions: Contains the Metric to be viewed

vWGTopBottom: WorkGroup Top Bottom.  Contains 0 for Top 5, 1 for Bottom 5.  Value is set via trigger in buttons.

The variables are set as vCallsExpression: Calls Offered and vWGTopBottom: 1.

Expression:

If(vCallsExpression = 'Calls Offered' and vWGTopBottom = 0,Num(If(Rank( sum({<ACDCalls = {">0"}>} ACDCalls)) <= 5, sum({<ACDCalls = {">0"}>} ACDCalls)),'#,##0'),

If(vCallsExpression = 'Calls Offered' and vWGTopBottom = 1,Num(If(Rank(-sum({<ACDCalls = {">0"}>} ACDCalls)) <= 5, sum({<ACDCalls = {">0"}>} ACDCalls)),'#,##0'),

If(vCallsExpression = 'AHT' and vWGTopBottom = 0,Interval(If(Rank(-sum(AHTime) / sum(ACDCalls)) <= 5,sum(AHTime) / sum(ACDCalls)),'h:mm:ss'),

If(vCallsExpression = 'AHT' and vWGTopBottom = 1,Interval(If(Rank( sum(AHTime) / sum(ACDCalls)) <= 5,sum(AHTime) / sum(ACDCalls)),'h:mm:ss'),

If(vCallsExpression = 'Hold' and vWGTopBottom = 0,Interval(If(Rank(-sum(HoldTime) / sum(ACDCalls)) <= 5,sum(HoldTime) / sum(ACDCalls)),'h:mm:ss'),

If(vCallsExpression = 'Hold' and vWGTopBottom = 1,Interval(If(Rank( sum(HoldTime) / sum(ACDCalls)) <= 5,sum(HoldTime) / sum(ACDCalls)),'h:mm:ss'),

If(vCallsExpression = 'ACW' and vWGTopBottom = 0,Interval(If(Rank(-(sum({<AUXcode = {'Extended Acw'}>} Duration) + sum(ACWTime)) / sum(ACDCalls)) <= 5,sum({<AUXcode = {'Extended Acw'}>} Duration) + sum(ACWTime)) / sum(ACDCalls),'h:mm:ss'),

If(vCallsExpression = 'ACW' and vWGTopBottom = 1,Interval(If(Rank(( sum({<AUXcode = {'Extended Acw'}>} Duration) + sum(ACWTime)) / sum(ACDCalls)) <= 5,sum({<AUXcode = {'Extended Acw'}>} Duration) + sum(ACWTime)) / sum(ACDCalls),'h:mm:ss')))))))))

Thanks in advance for any/all help.

Jason

1 Solution

Accepted Solutions
vamsee
Specialist
Specialist

Hi Jason,

I have added Aggr(, WorkGroup) to your third expression. (p.s: middle chart).

Looks like it worked. Please check.


And also to test out I have added another text box which initializes the variable vWGTopBottom=2 to test the third if statement and it worked.


Please check.

View solution in original post

7 Replies
vamsee
Specialist
Specialist

Hi Jason,

Your expression looks okay.

Please check if your data has correct formatting and also try adding else part in the last IF statement after Interval() and see if you get a default output.

jcampbell474
Creator III
Creator III
Author

Thank you, Vamsee.

I've been looking at it more and narrowed it down to this.

This works.  Notice, the last If statement simply returns 'yes'.  It actually stops at the 2nd If when vWGTopBottom = 1 and stops at the 1st when vWGTopBottom = 0.  Works fine.

If(vCallsExpression = 'Calls Offered' and vWGTopBottom = 0,Num(If(Rank( sum({<ACDCalls = {">0"}>} ACDCalls)) <= 5, sum({<ACDCalls = {">0"}>} ACDCalls)),'#,##0'),

If(vCallsExpression = 'Calls Offered' and vWGTopBottom = 1,Num(If(Rank(-sum({<ACDCalls = {">0"}>} ACDCalls)) <= 5, sum({<ACDCalls = {">0"}>} ACDCalls)),'#,##0'),

If(vCallsExpression = 'AHT' and vWGTopBottom = 0,'yes')))

This doesn't work.  Notice that the last statement returns sum(ACDCalls), else 0.  The entire object will not populate when an expression is in the 3rd If statement.  It will not work even if I remove the variables and simply put If(1=1,sum(ACDCalls)).

If(vCallsExpression = 'Calls Offered' and vWGTopBottom = 0,Num(If(Rank( sum({<ACDCalls = {">0"}>} ACDCalls)) <= 5, sum({<ACDCalls = {">0"}>} ACDCalls)),'#,##0'),

If(vCallsExpression = 'Calls Offered' and vWGTopBottom = 1,Num(If(Rank(-sum({<ACDCalls = {">0"}>} ACDCalls)) <= 5, sum({<ACDCalls = {">0"}>} ACDCalls)),'#,##0'),

If(vCallsExpression = 'AHT' and vWGTopBottom = 0,sum(ACDCalls),0)))

What is causing this?

vamsee
Specialist
Specialist

Can you please share your sample QVW. It might be an issue with your data types.

Coz I have generated some random numbers and checked your expression and it worked for me.

jcampbell474
Creator III
Creator III
Author

Here's a sample. 

Toggle using the Top and Bottom buttons.

vamsee
Specialist
Specialist

Hi Jason,

I have added Aggr(, WorkGroup) to your third expression. (p.s: middle chart).

Looks like it worked. Please check.


And also to test out I have added another text box which initializes the variable vWGTopBottom=2 to test the third if statement and it worked.


Please check.

jcampbell474
Creator III
Creator III
Author

Vamsee, that's it!  Thank you!!

I never thought AGGR would be the solution.  Why did it work?  Set analysis did not remove the aggregation (Workgroup is a dimension). 

Again, thank you!

vamsee
Specialist
Specialist

Good to hear it worked.

No Set didn't remove it. The IF statements did.

SUM(IF(Conditions, SUM(), NULL() )) would also do the trick in some situations.