How to filter a count with a condition

[mahieuphilippe]

Hi i have this result :

itemcode	distnumber	onhandqty
1800000038	127-07	952.540000
1800000038	127-08	952.540000
1800000038	127-09	952.540000
1800000038	127-10	952.540000
1800000038	127-13	952.540000
1800000038	127-14	952.540000
1800000038	127-15	952.540000
1800000038	127-16	952.540000
1800000038	127-17	952.540000
1800000038	127-18	952.540000
1800000038	127-19	952.540000
1800000038	127-20	952.540000
1800000038	128-01	952.540000
1800000038	128-02	952.540000
1800000038	128-03	952.540000
1800000038	128-04	952.540000
1800000038	128-05	952.540000
1800000038	128-06	952.540000
1800000038	128-07	952.540000
1800000038	128-08	952.540000
1800000038	128-09	952.540000
1800000038	128-10	952.540000
1800000038	128-11	952.540000
1800000038	128-12	952.540000
1800000038	128-13	952.540000
1800000038	128-14	952.540000
1800000038	128-15	952.540000
1800000038	128-16	952.540000
1800000038	128-17	952.540000
1800000038	128-18	952.540000
1800000038	128-19	952.540000
1800000038	128-20	952.540000
1800000040	132-35	400.000000
1800000040	132-36	400.000000
1800000040	132-37	400.000000
1800000040	132-38	400.000000
1800000040	132-39	400.000000
1800000040	132-40	400.000000
1800000040	133-01	400.000000
1800000040	133-02	400.000000
1800000040	133-03	400.000000
1800000040	133-04	400.000000
1800000040	133-05	400.000000
1800000040	133-06	400.000000
1800000040	133-07	400.000000
1800000040	133-08	400.000000
1800000040	133-09	400.000000
1800000040	133-10	400.000000
1800000040	133-11	400.000000
1800000040	133-12	400.000000
1800000040	133-13	400.000000
1800000040	133-14	400.000000
1800000040	133-15	400.000000
1800000040	133-16	400.000000
1800000040	133-17	400.000000
1800000040	133-18	400.000000
1800000040	133-19	400.000000
1800000040	133-20	400.000000
1800000040	133-21	400.000000
1800000040	133-22	400.000000
1800000040	133-23	400.000000
1800000040	133-24	400.000000
1800000040	133-25	400.000000
1800000040	133-26	400.000000
1800000040	133-27	400.000000
1800000040	133-28	400.000000
1800000040	133-29	400.000000
1800000040	133-30	400.000000
1800000040	133-31	400.000000
1800000040	133-32	400.000000
1800000040	133-33	400.000000
1800000040	133-34	400.000000
1800000040	133-35	400.000000
1800000040	133-36	400.000000
1800000040	133-37	400.000000
1800000040	133-38	400.000000
1800000040	133-39	400.000000
1800000040	133-40	400.000000

i  want only a résult by item code with only the min of the three first number of distnumber and a count of this condition so the result is :

into my table graphic i use 

first colum =  dimension 

second :  Min( SubField(distnumber, '-', 1) )  (take only the min of the distnumber)

last colum  :count(
{<
distnumber = {"*$(=Aggr(Min(Left(distnumber,3))), U_CapacItemcode)*"}
>} distnumber
)

i have this result  (no correct, the correct is  12  for the O-71 (1800000038) , here is the total of the distnumber 127 and 128 of the 1800000038  (O-71)

this is the correct result   how to have the good résult ?  thanks

1800000038	127	12
1800000040	132	6

[Nagaraju_KCS · Accepted solution]

Try this 

YourData:
Load * subfield(distnumber,, '-', 1) as dist_firstnumber;
Load * Inline [
itemcode, distnumber, onhandqty                                                                                                              ........................
];

MinValue:
Load itemcode,
         Min(dist_firstnumber) as distinctValue
resident Yourdata;

chart expression
Count({<dist_firstnumber=P(distinctValue)>}itemcode)

[mahieuphilippe]

thanks you !!!!

[thomasshellby]

Hello 👐

The problem is that Min(Left(distnumber,3)) is being looked at in the way inside the set analysis. What you really want to do is find the 3 digit prefix for each 'itemcode' first and then only count the records that have that prefix. 

I would not use 'Aggr()' inside the set modifier. It is better to calculate the prefix at the itemcode dimension level and use that value for the count. 

Example : 

Count(
  
  Aggr(
        If(
            Left(distnumber,3) = Min(TOTAL <itemcode> Left(distnumber,3)),
            distnumber
        ),
        itemcode,
        distnumber
    )
)

Or, if your table dimension is already itemcode, a cleaner approach is:

Count(
    If(
        Left(distnumber,3) = Min(TOTAL <itemcode> Left(distnumber,3)),
        distnumber
    )
)

For 1800000038, the minimum prefix is 127. Therefore, only the 127-* records are counted:

127-07 through 127-20 = 12 records
128-* records are ignored

So the result, for '18000000038' should be 12. 

If you also want the column to show the minimum 'distnumber' perfix you can use: 

The key thing to remeber is that the minimum is found within each 'itemcode' not for the whole dataset. This means 'Min' is calculated for each 'itemcode'. 

Hope this will help you 👍