Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I want to count how many of each PO Num exists so I did this simple straight table:
PO Num | Count (PO_Num) |
---|---|
1234 | 25 |
I did another straight table and added in the other variables:
Date | PO Num | Store | Line Cost | Unit Cost | Count (PO_Num) |
---|---|---|---|---|---|
30/09/14 | 1234 | A | 543.6 | 54.36 | 5 |
30/09/14 | 1234 | A | 543.6 | 54.36 | 5 |
30/09/14 | 1234 | A | 265.8 | 54.36 | 5 |
30/09/14 | 1234 | A | 265.8 | 54.36 | 5 |
30/09/14 | 1234 | A | 1653.2 | 54.36 | 5 |
May I know how to get this:
PO Num | Count (PO_Num) |
---|---|
1234 | 5 |
Date | PO Num | Store | Line Cost | Unit Cost | Count (PO_Num) |
---|---|---|---|---|---|
30/09/14 | 1234 | A | 543.6 | 54.36 | 1 |
30/09/14 | 1234 | A | 543.6 | 54.36 | 1 |
30/09/14 | 1234 | A | 265.8 | 54.36 | 1 |
30/09/14 | 1234 | A | 265.8 | 54.36 | 1 |
30/09/14 | 1234 | A | 1653.2 | 54.36 | 1 |
I've already tried formulas such as Count(PO_Num), textcount(PO_Num) and aggr(Count(PO_Num),PO_Num,Store,Date) but I still got 25 as the total count.
Thanks.
Hi,
Try like this
For Table1 :
=Max(Aggr(Count(PO_Num), Date, PO_Num, Store, [Line Cost], [Unit Cost]))
For Table 2:
Count(DISTINCT PO_Num)
Hope this helps you.
Regards,
Jagan.
Thanks, but if I use count(distinct the result will only be 1 record for that PO_Num, but it's supposed to be 5 records
HI,
For this use : =Max(Aggr(Count(PO_Num), Date, PO_Num, Store, [Line Cost], [Unit Cost]))
PO Num | Count (PO_Num) |
---|---|
1234 | 5 |
For this : Count(DISTINCT PO_Num) - Using distinct you will get 1, I think this is what you are expecting.
Date | PO Num | Store | Line Cost | Unit Cost | Count (PO_Num) |
---|---|---|---|---|---|
30/09/14 | 1234 | A | 543.6 | 54.36 | 1 |
30/09/14 | 1234 | A | 543.6 | 54.36 | 1 |
30/09/14 | 1234 | A | 265.8 | 54.36 | 1 |
30/09/14 | 1234 | A | 265.8 | 54.36 | 1 |
30/09/14 | 1234 | A | 1653.2 | 54.36 | 1 |
Regards,
Jagan.
Thanks, but actually I only want this table:
PO Num | Count (PO_Num) |
---|---|
1234 | 5 |
This larger table is only for me to check why the count of PO_Num is 25 instead of 5:
Date | PO Num | Store | Line Cost | Unit Cost | Count (PO_Num) |
---|---|---|---|---|---|
30/09/14 | 1234 | A | 543.6 | 54.36 | 1 |
30/09/14 | 1234 | A | 543.6 | 54.36 | 1 |
30/09/14 | 1234 | A | 265.8 | 54.36 | 1 |
30/09/14 | 1234 | A | 265.8 | 54.36 | 1 |
30/09/14 | 1234 | A | 1653.2 | 54.36 | 1 |
I still don't get my desired table using ' =Max(Aggr(Count(PO_Num), Date, PO_Num, Store, [Line Cost], [Unit Cost]))'.
Is there any chance you might know why each record for PO_Num '1234' is duplicated 5 times? Because now I am getting this table:
PO Num | Count (PO_Num) |
---|---|
1234 | 25 |
And to see why there are 25 records for PO_Num '1234', I did this next table:
Date | PO Num | Store | Line Cost | Unit Cost | Count (PO_Num) |
---|---|---|---|---|---|
30/09/14 | 1234 | A | 543.6 | 54.36 | 5 |
30/09/14 | 1234 | A | 543.6 | 54.36 | 5 |
30/09/14 | 1234 | A | 265.8 | 54.36 | 5 |
30/09/14 | 1234 | A | 265.8 | 54.36 | 5 |
30/09/14 | 1234 | A | 1653.2 | 54.36 | 5 |
Thanks,
Hi,
You have different line costs that is the PO_Num count is duplicating.
Regards,
Jagan.