I would like to calculate the number of weeks for each store where there are sales. In order to do this, I have to calculate the number of non-empty records for each store across all the weeks. I have 34 weeks. So the maximum of non empty records should be 34. I have used the following formula:
You do not need formula for this - use Aggregation in Table View to aggregate on store name [Store Description] and choose 'Unique value count' as function for [Week] field. Maximum count is 34 per shop. Make sure that in Tools menu>function>formula is calculated over group results.
Mohammed - You can also do this with a Subset formula. When composing a Subset formula, or any formula, it is easiest to do (and de-bug) if you create a separate working tab, add a Table View and Sidebars, but show only the fields and filters referred to in the formula. Attached is your file with a working tab added showing your original formula and also the correct one