topic Re: Script Logic in QlikView

Script Logic

tmumaw — Mon, 27 Nov 2017 15:48:28 GMT

Hi Group,

I have a question for all of you. I need to get some statistics based on the following:

What % of ServLoc have 4+ MG_Key proposed?
What % of ServLoc have only MG_Key = 002 proposed?
What % of ServLoc have only MG_Key = 004 proposed?
What % of ServLoc have only MG_Key = 010 proposed?
What % of ServLoc have MG_Key all (002, 004, 010) proposed?

Here is my table:

Oppt_Summ:
Load
ServiceLocation as ServLoc,
ContractStart_Year as Cont_Year,
Left([Material Grp],3) as MG_Key,
[Material Grp] as MGrp,
NetSales as Net_Sales
Resident Opportunity;

Any ideas?

Thanks

Re: Script Logic

YoussefBelloum — Mon, 27 Nov 2017 16:14:48 GMT

hi,

try to share sample data

Re: Script Logic

shraddha_g — Mon, 27 Nov 2017 16:21:34 GMT

Try Below:

1. Count({<ServLoc = { Count(MG_Key)>=4 }>}ServLoc)/Count(Total ServLoc)

2. Count({<MG_Key = { '002' }>}ServLoc)/Count(Total ServLoc)

3.Count({<MG_Key = { '004' }>}ServLoc)/Count(Total ServLoc)

4.Count({<MG_Key = { '004','002','010' }>}ServLoc)/Count(Total ServLoc)

Else Share sample data

Re: Script Logic

tmumaw — Mon, 27 Nov 2017 16:22:45 GMT

Pretty simple data.

ServLoc Cont_Year MG_Key MGrp Net Sales

123 2018 002 Pest 25.00

123 2018 004 Fert 125.00

123 2018 010 Lawn 125.00

456 2018 002 Pest 50.00

456 2018 010 Lawn 175.00

789 2018 004 Fert 35.00

789 2018 010 Lawn 165.00

025 2018 010 Lawn 195.00

Re: Script Logic

tmumaw — Mon, 27 Nov 2017 16:25:21 GMT

This would work however if you look at the first post the word "Only" comes into play.

Re: Script Logic

YoussefBelloum — Mon, 27 Nov 2017 16:37:13 GMT

find attached inspiration for your first point:

Re: Script Logic

tmumaw — Mon, 27 Nov 2017 17:24:20 GMT

I don't think it's quite right. There are 26 servloc and 10 have 4 or more MG_Key

Re: Script Logic

YoussefBelloum — Mon, 27 Nov 2017 17:36:14 GMT

Ok, please re attach the app with complete data of the 26 servloc having with 10 of them having 4 or more MG_Key

Re: Script Logic

rubenmarin — Mon, 27 Nov 2017 18:08:24 GMT

Hi, maybe with a distinct count?:

=count({<ServLoc={"=count(Distinct {<ServLoc>}MG_Key)>=4"}>}ServLoc)/Count(TOTAL ServLoc)

Re: Script Logic

tmumaw — Mon, 27 Nov 2017 18:14:20 GMT

Thanks Ruben that was it.

Re: Script Logic

tmumaw — Mon, 27 Nov 2017 18:32:45 GMT

Thought it was right but I was wrong. Here is some sample data.

Re: Script Logic

tmumaw — Mon, 27 Nov 2017 18:54:10 GMT

I think I resolved the previous issue by removing the ServLoc. Any ideas on the other ones?

=count({<ServLoc={"=count(distinct MG_Key)>=4"}>}ServLoc) / Count(Distinct TOTAL ServLoc) (new one)

=count({<ServLoc={"=count(distinct{<ServLoc>}MG_Key)>=4"}>}ServLoc) / Count(Distinct TOTAL ServLoc) (old one)

Re: Script Logic

tmumaw — Mon, 27 Nov 2017 19:11:01 GMT

I attached some data. Any ideas on how to get these to work?

What % of ServLoc have only MG_Key = 002 proposed?

What % of ServLoc have only MG_Key = 004 proposed?
What % of ServLoc have only MG_Key = 010 proposed?
What % of ServLoc have MG_Key all (002, 004, 010) proposed? !

Re: Script Logic

rubenmarin — Mon, 27 Nov 2017 20:54:17 GMT

Hi Thom, maybe adding more distinct:

count(DISTINCT {<ServLoc={"=count(Distinct {<ServLoc>}MG_Key)>=4"}>}ServLoc)/Count(DISTINCT ServLoc)

Or wich ones are failing and what's the expected result?