topic Help with Nested If Expression in QlikView

Help with Nested If Expression

jcampbell474 — Thu, 16 Aug 2018 14:21:04 GMT

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

Re: Help with Nested If Expression

vamsee — Thu, 16 Aug 2018 15:05:42 GMT

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.

Re: Help with Nested If Expression

jcampbell474 — Thu, 16 Aug 2018 15:14:36 GMT

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?

Re: Help with Nested If Expression

vamsee — Thu, 16 Aug 2018 15:25:10 GMT

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.

Re: Help with Nested If Expression

jcampbell474 — Thu, 16 Aug 2018 16:31:10 GMT

Here's a sample.

Toggle using the Top and Bottom buttons.

Re: Help with Nested If Expression

vamsee — Thu, 16 Aug 2018 17:26:03 GMT

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.

Re: Help with Nested If Expression

jcampbell474 — Thu, 16 Aug 2018 17:44:49 GMT

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!

Re: Help with Nested If Expression

vamsee — Thu, 16 Aug 2018 18:26:07 GMT

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.