I need a formula to count

[GregRyder]

I have the below formula the searches through my data to find the most common retail selling price

Mode( {<[MEMBER CODE]=>} [Store Retail])

I now need a formula to tell me how many stores have that common retail price

[Or]

Probably easiest and cleanest to achieve this with dollar-sign expansion in another set analysis:
Count(DISTINCT {< [Store Retail] = {"=$(=Mode({<[MEMBER CODE]=>} [Store Retail]))"} >} Store)
I can't test this without the data, but I think that's the right syntax here.

[GregRyder]

Hi

Thanks.

This is the error I get is

[Or]

Is your field name "Store"?

[GregRyder]

The field I am comparing is ([Store Retail]) and it is reading from all the individual store files.

Below is the load for the store data

MICA_SOH_DATA:
LOAD
Model as ID,
CODE as CODE,
TRIM(Text(BARCODE)) as [BARCODE],
ONHAND as [QTY ON HAND],
SELLPINC1 as [Store Retail],
GP_1 as [Store GP],
AVRGCOST as [Store Cost],
'$(vStoreCode)' as [MEMBER CODE]
FROM [lib://Data:Amazon_S3_V2_member-dashboard-qlik-export/$(vStoreCode)/001/Stock.csv]
(txt, codepage is 28591, embedded labels, delimiter is ';', msq)
Where Exists(BARCODE,TRIM(Text(BARCODE)))

[Or]

This doesn't tell which field is the store ID, but I guess [MEMBER CODE] is the most likely suspect here, so:

Count(DISTINCT {< [Store Retail] = {"=$(=Mode({<[MEMBER CODE]=>} [Store Retail]))"} >} [MEMBER CODE])

[GregRyder]

Hi

Yes, the field we need to count is [MEMBER CODE]. the new formula is now not giving errors, but it is counting all the stores that have a price for the product and not the just the stores with the Comon price. 

As per this formula Mode( {<[MEMBER CODE]=>} [Store Retail])

[GregRyder]

As per the screen shot below 29 stores support this product and the most common price is 36.99. and what i am trying to conclude is how many stores have that price

[GregRyder · Accepted solution]

Hi All,

I have finally worked out the formula.

Sum( Aggr(If(Only([Store Retail])=Mode(total<BARCODE,[Supplier Name]> [Store Retail]),1,0),[MEMBER CODE],BARCODE,[Supplier Name]))

Thanks for the ideas