Count of Customer

Our customer number is a combine of Billto & Shipto, therefore I created a custom attribute "CustID" as ([oeeh_custno] + ' ' + [oeeh_shipto]) , now i need get the distinct count of "CustID", can i get it by report expression? if it can't be done, how to create it as custom measure?

Find more posts tagged with

Birst - General Discussions

Comments

Legacy Contributor

If [oeeh_custno] and [oeeh_shipto] are both measures in the fact table then you could create a Custom Measure the same way you created a Custom Attribute. If that's not an option you could do a STAT expression in your report, which would be something like the following:

NOTE: I am assuming the dimension this CustID is in is the OEEH dimension. If it is in a different dimension, change that part of this expression.

STAT(CountDistinct, 0, SELECT [OEEH.CUSTID] from [All])

0712131256598360.doc

Legacy Contributor

Thank you Cindy,

Unfortunately the [oeeh_custno] and [oeeh_shipto] are not measures in the fact table.

I used the STAT function, and this is what i get, is it possible to get the correct count?

Thanks!

Legacy Contributor

I am assuming that "Cust Cnt" is the STAT expression and that you used the expression I suggested. If so, it is giving you the total distinct count of CustID, which is what you asked for initially. I don't know what you would consider to be the "correct" count. What exactly are you trying to count, and by what? Do you want a count of "ooh_shipto" for each "ooh_custno"? It's not clear what you are trying to accomplish. Column values can be passed into the stat and used in a where clause to return the distinct count per something, but I don't know what you are trying to do so it's hard to help.

If you would share what other columns you are including in the report, what you want a discount count of, and broken out by what other columns? You could include the BQL query from your report (click on the icon that looks like tools then click on Query menu item and copy the query from there).

Legacy Contributor

For above test I was expecting result of 1 for each row.

Anyway, below is what I really want, query is copied below:

SELECT TOP 5 USING OUTER JOIN STAT (CountDistinct, 0, SELECT [OEEH.HDS_CustID] from [All]) 'COL0' , [Time.Year] 'COL1' , [oeeh_invoicedt: # Distinct orderno] 'COL2' FROM [ALL] WHERE ( ( [Time.Year]=2022 OR [Time.Year]=2021 ) )

Copyright © 2025 Infor. All rights reserved.