Visokio website     Downloads     Video tutorials     KnowledgeBase  
Filtering: Dynamic record counts on filtered data? - Visokio Forums
Filtering: Dynamic record counts on filtered data?
  •     dpawley August 29, 2014 7:34AM
    Hi

    I have a large dataset of 400k records over 12 months, by supplier, by product, by outlets. What I would like to get to is "Product A is in 50% of the outlets, over 12 months. However in August it was in 80%" from the dataset.

    I can separate out the data in a chart that ranks the products by volume top to bottom over the past 12 months. I have used Unique_Count to get the number of outlets that are stocking Product A which is great but I want to divide this number by the total number of outlets in the dataset and have it change if I was to select one month, or 2 months (i.e. filter).

    Thanks

    Danny
    Attachments
    Omniscope Eg.xlsx 9K
  • 3 Comments
  •     paola September 11, 2014 10:42AM
    In a 'real dataset' you probably wouldn't have empty rows, where Product A/Month X sales are empty. In that case your formula would look like this:
    SUBSET_UNIQUECOUNT([Outlet], SUBSET2([Product],[Month]))
    /
    SUBSET_UNIQUECOUNT([Outlet])


    In the attached demo case, presence of Product A in Outlet X has to be established by evaluating the sales Volume, so additional criteria is added:
    SUBSET_UNIQUECOUNT([Outlet], SUBSET3([Product],[Month],[Volume],[Product],[Month],0,"=","=",">"))
    /
    SUBSET_UNIQUECOUNT([Outlet])


    If you want dynamic results, you can define a variable "Month Choice", and place it inside the formula as a user-selected value:
    SUBSET_UNIQUECOUNT([Outlet], SUBSET2([Product],[Month],[Product],{Month Choice}))


  •     dpawley September 29, 2014 11:59AM
    Hi Paola

    I have had a look at all the above and I can't seem to make any of them work at all. I keep getting error messages. I would like to show you my live example if that id possible as see if it makes a bit more sense as it has confidential sales data in it.

    Can I email this over and then update the forum once we have had chance to investigate?

    Thanks

    Danny
  •     paola September 29, 2014 12:14PM
    Yes please, do email us the actual dataset you wish to use for this analysis and we will advise how to apply the formulas. (please email us directly @ support)
    Thanks

Welcome!

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

Sign In Apply for Membership