SUBSET functions: Non-empty record count?

mohamed · October 17, 2012 5:34AM

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:

"SUBSET_NONEMPTYCOUNT([Sumofvalue], SUBSET2([Store Description],[Week]))"

Please see attached file and look at the formula "Week Count", the top value should return 34 but it returns 52. I am not sure where I am going wrong.

Please see the Pivot view, it shows the Weeks against all the stores where I see empty record, I would like to discard in the calculations.

Regards, Mohamed

paola · October 17, 2012 6:03AM

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.

tjbate · October 17, 2012 6:38AM

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

mohamed · October 17, 2012 6:46AM

Thank you very much.
That was simpler than I thought :)

Have a good day.

Welcome!

Categories

Ideas Parade

Tagged