Visokio website     Downloads     Video tutorials     KnowledgeBase  
Subset formula: Calculating % change over time - Visokio Forums
Subset formula: Calculating % change over time
  • Phill February 28, 2017 5:59AM
    I am looking for a formula to calculate the change in number of customers using a certain product over 3 time periods. I have the total number of people in my dataset; which is also the sum of people in 3 quarters (Q1 2016, Q2 2016, Q3 2016).
    I would like to show the % change in number of people using the product from the previous quarter.
    How would I do this?
  • 4 Comments
  •     paola February 28, 2017 6:38AM
    Please post a sample/dummy dataset so we can send you the formula.
    Some examples in other posts:
    http://forums.visokio.com/discussion/comment/9759/#Comment_9759
    http://forums.visokio.com/discussion/comment/6514/#Comment_6514
  • Phill February 28, 2017 9:36AM
    Here is an example:
    # Quarter Product
    53 2016 Q1 Product 1
    79 2016 Q1 Product 2
    100 2016 Q1 Product 3
    44 2016 Q2 Product 1
    15 2016 Q2 Product 2
    108 2016 Q2 Product 3
    68 2016 Q3 Product 1
    0 2016 Q3 Product 2
    130 2016 Q3 Product 3
  •     paola February 28, 2017 1:27PM
    Please see file attached. Formulas are in the DataManager space, so will continue to work with the new data coming in. This being a dummy set, periods are running just Q1-Q3. If your dataset stretched over several years, you will need to add a field to dynamically rank the quarters, so you get continuous evaluation from Q4-Q1 next year, for example.
    Formulas used:

    %QoQ difference
    IF(
    [Quarter number]>1
    ,
    (SUBSET_SUM([Value], SUBSET3([Product],[Year],[Quarter number]))
    -
    SUBSET_SUM([Value], SUBSET3([Product],[Year],[Quarter number],[Product],[Year],[Quarter number]-1))
    )
    /
    SUBSET_SUM([Value], SUBSET3([Product],[Year],[Quarter number],[Product],[Year],[Quarter number]-1))
    ,
    null)

    Market share Product/Quarter
    SUBSET_SUM([Value], SUBSET3([Product],[Year],[Quarter number]))
    /
    SUBSET_SUM([Value], SUBSET2([Year],[Quarter number]))

    image
  • Phill March 2, 2017 6:38AM
    Thank you! The formula worked well!

Welcome!

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

Sign In Apply for Membership