Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Help with Nested If Expression

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

jcampbell474

Creator III

2018-08-16
10:21 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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,249 Views

1 Solution

Accepted Solutions

vamsee

Specialist

2018-08-16
01:26 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Specialist

2018-08-16
11:05 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

1,095 Views

jcampbell474

Creator III

2018-08-16
11:14 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?

1,095 Views

vamsee

Specialist

2018-08-16
11:25 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

1,094 Views

jcampbell474

Creator III

2018-08-16
12:31 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Here's a sample.

Toggle using the Top and Bottom buttons.

1,094 Views

vamsee

Specialist

2018-08-16
01:26 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2018-08-16
01:44 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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!

1,094 Views

vamsee

Specialist

2018-08-16
02:26 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.