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: 
Anonymous
Not applicable

return the value that matched the category.

Hi Everyone,

I'm really new in Qlik. would like to return the data with the below conditions.

I have attached the data with 2 tabs:

1. sample data

2. desired result

Condition:
Return CLI for 1st Participant "customer" Purpose INBOUND Direction
Return CLI for "customer" Participant Purpose OUTBOUND Direction

luismadriz‌ thanks!

1 Solution

Accepted Solutions
luismadriz
Specialist
Specialist

Hi Isabel,

Please create a straight table with all the fields in the desired result as Dimensions and only CLI as a measure with the following

=FirstSortedValue( DISTINCT {<[Participant Purpose]={customer},[Conversation Direction]={Inbound,Outbound}>} CLI, Date)

You should get the desired result. I just tried it and it worked

Regards,

Luis

Untitled.png

View solution in original post

13 Replies
luismadriz
Specialist
Specialist

Hi Isabel,

Please create a straight table with all the fields in the desired result as Dimensions and only CLI as a measure with the following

=FirstSortedValue( DISTINCT {<[Participant Purpose]={customer},[Conversation Direction]={Inbound,Outbound}>} CLI, Date)

You should get the desired result. I just tried it and it worked

Regards,

Luis

Untitled.png

Anonymous
Not applicable
Author

Hi Luis,

The AHT, ATT, Hold and ACW there are measures.

I'll try if this will still work. (fingers crossed)

=FirstSortedValue( DISTINCT {<[Participant Purpose]={customer},[Conversation Direction]={Inbound,Outbound}>} CLI, Date)

Anonymous
Not applicable
Author

Everything were deleted

cli.JPG

luismadriz
Specialist
Specialist

Ok, for each of those measures update as per the following example

Sum({<[Participant Purpose]={customer},[Conversation Direction]={Inbound,Outbound}>} ACW)

Please let me know,

Luis

Anonymous
Not applicable
Author

I need to change the ACW on this expression.

Sum({<[Participant Purpose]={customer},[Conversation Direction]={Inbound,Outbound}>}ACW)


ACW on my measure is

num((sum([Aggr tAcw]/1000)/sum([Aggr nHandle])),vFormatSeconds2)


when I use this, it's giving me Error.

Sum({<[Participant Purpose]={customer},[Conversation Direction]={Inbound,Outbound}>}num((sum([Aggr tAcw]/1000)/sum([Aggr nHandle])),vFormatSeconds2))


I'm so sorry if i still need to ask you this. I'm just really new.

luismadriz
Specialist
Specialist

Try this:

num((sum({<[Participant Purpose]={customer},[Conversation Direction]={Inbound,Outbound}>} [Aggr tAcw]/1000)/sum({<[Participant Purpose]={customer},[Conversation Direction]={Inbound,Outbound}>} [Aggr nHandle])),vFormatSeconds2)

Anonymous
Not applicable
Author

Hi Luis,

The highlighted is not showing the correct data.

it should be returning this CLI instead

+61383634860

cliv2.JPG

luismadriz
Specialist
Specialist

Good you have most of it working,

Without a proper field to determine which one goes first it won't be possible.

Ideally an order field should be placed in the the data. As those calls happened at the same date and at the same time, there is no way to confirm for sure.

My suggestion is that you try to get to the load script to add a field in the data that could represent the load order especially for those calls that happened the same date and the same time

I hope this helps,

Cheers,

Luis

PS. When applicable please replies as Helpful if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as Helpful if you feel additional info is useful to others

Anonymous
Not applicable
Author

Thanks Luis!

Can I still get back to you once I have placed the additional data field for load order?