topic Set analysis Expression Challenge in QlikView

Set analysis Expression Challenge

chaitanyajami — Fri, 10 Mar 2017 05:32:10 GMT

Hi all,

I have a challenge like iam calculating count of [Quote Number DMP] when [Quote Number Status]= Approved like below

count({<[Quote Status]={'APPROVED'}>}[Quote Number DMP])

now my requirement is to include another condition where count to be calculated when the field value [Approver job name] equals to [Requester job name], kindly suggest how can i achieve it

Re: Set analysis Expression Challenge

rahulpawarb — Fri, 10 Mar 2017 05:51:01 GMT

Hello Chaitanya,

Below is the draft version of expression:

If([Approver job name]=[Requester job name], count({<[Quote Status]={'APPROVED'}>}[Quote Number DMP]))

Could you please share the application with sample data? This will help me to provide you more accurate solution.

Regards!

Rahul

Re: Set analysis Expression Challenge

chaitanyajami — Fri, 10 Mar 2017 06:17:55 GMT

Hi Rahul,

Here is my sample data looks like(i cannot copy app due to security restrictions) , and i tried the above expression somehow it was not working

Re: Set analysis Expression Challenge

rahulpawarb — Fri, 10 Mar 2017 06:29:17 GMT

Hello Chaitanya,

As I could see that you have list boxes for Approver Job Name & Requester Job Name fields. Where exactly you are planning to use this expression? If it is inside straight table then what other dimensions are there?

Regards!

Rahul

Re: Set analysis Expression Challenge

settu_periasamy — Fri, 10 Mar 2017 06:37:49 GMT

Hi,

May be try like this

=count({<[Quote Status]={'APPROVED'},

KeyField={'=[Approver job name]=[Requester job name]'}>}

[Quote Number DMP])

Re: Set analysis Expression Challenge

chaitanyajami — Fri, 10 Mar 2017 07:13:00 GMT

Hi Rahul,

basically when 'Approver Job Name' value equal to 'Requester Job Name' value then we are calculating the count of 'Quote Number DMP' (with condition [Quote Status]={'APPROVED'}), we are showing this in a textbox

Re: Set analysis Expression Challenge

chaitanyajami — Fri, 10 Mar 2017 07:14:05 GMT

Hi,

key field is not there , we just need to calculate when 'Approver Job Name' value equal to 'Requester Job Name' value then count of 'Quote Number DMP' (with condition [Quote Status]={'APPROVED'})

Re: Set analysis Expression Challenge

settu_periasamy — Fri, 10 Mar 2017 07:28:30 GMT

Have you tried the Rahul's expression?

also check this..

=Count(Aggr(if([Approver job name]=[Requester job name] and [Quote Status]='APPROVED',[Quote Number DMP]),[Approver job name],[Requester job name]))

Re: Set analysis Expression Challenge

chaitanyajami — Fri, 10 Mar 2017 08:15:01 GMT

Hi,

I have tried Rahul's expression it was not working, Ok i will try this Thank you

Re: Set analysis Expression Challenge

sunny_talwar — Fri, 10 Mar 2017 12:12:09 GMT

Give this a try as well

Count({<[Quote Status] = {'APPROVED'}>} If([Approver job name] = [Requester job name], [Quote Number DMP]))

Re: Set analysis Expression Challenge

sunny_talwar — Fri, 10 Mar 2017 12:13:31 GMT

Or if [Approver job name] = [Requester job name] are coming from the same table in the backend, then create a flag in the script

LOAD [Approver job name],

[Requester job name],

If([Approver job name] = [Requester job name], 1, 0) as Flag

....

FROM ...

and then this

Count({<[Quote Status] = {'APPROVED'}, Flag = {1}>} [Quote Number DMP])

Re: Set analysis Expression Challenge

chaitanyajami — Tue, 14 Mar 2017 04:56:42 GMT

Hi Sunny, Thanks for response i did the same thing and it is working fine now.