topic Re: count & max in QlikView

count & max

Anonymous — Tue, 06 Sep 2016 03:56:42 GMT

hi,

i have feild in the line order with value 1 or 2.

i want to calculte in%

exm:

order num 1 - i have 5 lines with 1 & 1 line with 2

order num 2 - i have 5 lines with 1 & 0 line with 2

order num 3 - i have 0 lines with 1 & 4 line with 2

i want to know in % how many orders have only 2 in that feild - in the exm 33%

how do i write it?

thanks'

yarin.

Re: count & max

tresB — Tue, 06 Sep 2016 04:55:55 GMT

Instead of writing the data in words, could you provide a sample of data? That would help us understand better.

Re: count & max

Anonymous — Tue, 06 Sep 2016 05:04:54 GMT

order	line order	test field
1	1	1
1	2	1
1	3	1
1	4	1
1	5	1
1	6	2
2	1	2
2	2	2
2	3	2
2	4	1
2	5	1
3	1	2
3	2	2
3	3	2
3	4	2
3	5	2
3	6	2

how many orders have only 2 value on test field ?

Re: count & max

tresB — Tue, 06 Sep 2016 05:11:00 GMT

Try like:

Count( Distinct {<[test field]={2}>} order)

Re: count & max

Anil_Babu_Samineni — Tue, 06 Sep 2016 06:09:16 GMT

Perhaps This:

Count( {<order = P({order = {'2'}}}), [test field]>} [test field])count & max

Re: count & max

soloeeeoff — Tue, 06 Sep 2016 06:48:53 GMT

This Problem can only be solved by the load statements as follows..

tab1:

LOAD order,

[line order],

[test field]

FROM

(ooxml, embedded labels, table is Sheet1);

tab2:

load

order as distinct_order,

only([test field])as cnt

Resident tab1

Group by order

;

Re: count & max

Jason_Michaelides — Tue, 06 Sep 2016 07:31:56 GMT

Try this:

Sum(Aggr(If(Concat(DISTINCT [Test field],',')=2,1,0),Order))

Hope this helps,

Jason

Re: count & max

effinty2112 — Tue, 06 Sep 2016 08:37:33 GMT

Hi Yarin,

Try this. It's similar to Anil's but I think we need to exclude line orders that have any test field = 1 rather than include any that have test field = 2.

=count({$<[line order]= E({<[test field] = {1}>})>}DISTINCT [line order])

EDIT : I think this is correct as we are trying to count order not line order.

=count({$<order= E({<[test field] = {1}>})>}DISTINCT order)

Kind regards

Andrew

Re: count & max

Jason_Michaelides — Tue, 06 Sep 2016 08:53:32 GMT

Potential problem with this solution is it assumes 1 and 2 are the only possible values for the test field. If that's the case then great!

Re: count & max

effinty2112 — Tue, 06 Sep 2016 09:17:54 GMT

"i have feild in the line order with value 1 or 2."

Re: count & max

effinty2112 — Tue, 06 Sep 2016 15:44:51 GMT

Dear Solomon,

That's a rather bold statement "This Problem can only be solved by the load statements as follows.."

The original post said that [test field] held only two values so we needn't consider other values, but if we do the expression below works, including for your extra records with [test field] = 3:

=count({$<order= E({<[test field] = {$(=Concat(DISTINCT if(Not Match([test field],2),[test field]),','))}>})>}DISTINCT order)

Kind regards

Andrew

Re: count & max

soloeeeoff — Wed, 07 Sep 2016 06:08:51 GMT

Thanks my beloved brother Andrew.

Re: count & max

soloeeeoff — Thu, 15 Sep 2016 05:22:19 GMT

Hi Yarin,

I really wanna thank You for asking this question.

Today I found a solution.

1. Use Text Box

2. type as

=count({1} Aggr(Only({1} [test field]), order))

This solves the problem

Thanks,

Re: count & max

soloeeeoff — Thu, 15 Sep 2016 05:23:54 GMT

Hi Andrew,

The following statement solves the problem ( Use Text Box)

=count({1} Aggr(Only({1} [test field]), order))

Re: count & max

soloeeeoff — Thu, 15 Sep 2016 05:28:59 GMT

If it exactly for the Test order =2

then use

=count({1<[test field]={'2'}>} Aggr(Only([test field]), order))

Thanks

Re: count & max

effinty2112 — Thu, 15 Sep 2016 07:49:06 GMT

Hi Solomon,

I like your idea of aggregating Only(). It's a neat solution.

Kind regards

Andrew