topic Re: Percentage seggregation based on ID field in QlikView

Percentage seggregation based on ID field

rajinikanth — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have data as below

Load * inline

[

ID,Region,complete_Date

IB11,asia,1/jan/2018

IB2,asia,2/feb/2018

IB3,asia,4/feb/2018

IB4,asia,6/mar/2018

IB5,usa,9/feb/2019

IB6,usa,12/feb/2019

IB7,usa,14/mar/2019

];

Expected OutPut

When i select asia i need the date when 75 % of my asias IDs are going to be completed.(output 4/feb/2018)

When i select usa i need the date when 100 % of my usa IDs are going to be completed.(output 14/mar/2018)

Re: Percentage seggregation based on ID field

vishsaggi — Thu, 09 Aug 2018 15:52:43 GMT

Can you elaborate why you have to get output as 4/feb/2018? And how did you calculate 75% will be completed?

Re: Percentage seggregation based on ID field

rajinikanth — Thu, 09 Aug 2018 19:38:12 GMT

Yeah its an oral calculation

Say i have 10 ids 50 percent of 10 ids is 5 so i need the date on 5th id

Basically a rownum would work here i guess

Re: Percentage seggregation based on ID field

vishsaggi — Thu, 09 Aug 2018 20:39:18 GMT

Ok will check it.

Re: Percentage seggregation based on ID field

vishsaggi — Thu, 09 Aug 2018 21:41:10 GMT

Try this?

Aggr:

LOAD *, AutoNumber(Region&ID1, Region) AS RowNum;

Load ID1, Trim(Region) AS Region,complete_Date, Date#(complete_Date, 'DD/MMM/YYYY') AS DateID inline

[

ID1,Region,complete_Date

IB11,asia,1/jan/2018

IB2,asia,2/feb/2018

IB3,asia,4/feb/2018

IB4,asia,6/mar/2018

IB5,usa,9/feb/2019

IB6,usa,12/feb/2019

IB7,usa,14/mar/2019

];

Then add straight table object.

Add Dim

Region, ID1

Expr:

= Pick(Match(Region, 'asia', 'usa'), IF(RowNum = (75/100)*Count(TOTAL {<Region = {'asia'}>} ID1), complete_Date),

IF(RowNum = Count(TOTAL {<Region = {'usa'} >} ID1), complete_Date))

You should see below: