Visokio website     Downloads     Video tutorials     KnowledgeBase  
SUBSET functions: Non-empty record count? - Visokio Forums
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.


It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership