count & max

[Anonymous]

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.

[tresesco]

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

[Anonymous]

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 ?

[tresesco]

Try like:

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

[Anil_Babu_Samineni]

Perhaps This:

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

[soloeeeoff]

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

;

[Jason_Michaelides]

Try this:

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

Hope this helps,

Jason

[effinty2112]

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

[Jason_Michaelides]

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! 

[effinty2112]

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