topic Re: I need a formula to count in App Development

I need a formula to count

GregRyder — Wed, 18 Mar 2026 08:26:34 GMT

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

Re: I need a formula to count

Or — Wed, 18 Mar 2026 10:07:08 GMT

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.

Re: I need a formula to count

GregRyder — Wed, 18 Mar 2026 10:20:30 GMT

Thanks.

This is the error I get is

Re: I need a formula to count

Or — Wed, 18 Mar 2026 10:27:27 GMT

Is your field name "Store"?

Re: I need a formula to count

GregRyder — Wed, 18 Mar 2026 10:36:37 GMT

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)))

Re: I need a formula to count

Or — Wed, 18 Mar 2026 10:43:49 GMT

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])

Re: I need a formula to count

GregRyder — Wed, 18 Mar 2026 11:03:26 GMT

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])

Re: I need a formula to count

GregRyder — Wed, 18 Mar 2026 11:27:28 GMT

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

Re: I need a formula to count

GregRyder — Fri, 20 Mar 2026 06:35:34 GMT

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