Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
jcampbell474
Contributor 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

Tags (1)
1 Solution

Accepted Solutions
vamsee
Valued Contributor

Re: Help with Nested If Expression

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.

7 Replies
vamsee
Valued Contributor

Re: Help with Nested If Expression

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
Contributor III

Re: Help with Nested If Expression

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
Valued Contributor

Re: Help with Nested If Expression

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
Contributor III

Re: Help with Nested If Expression

Here's a sample. 

Toggle using the Top and Bottom buttons.

vamsee
Valued Contributor

Re: Help with Nested If Expression

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
Contributor III

Re: Help with Nested If Expression

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
Valued Contributor

Re: Help with Nested If Expression

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.