Visokio website     Downloads     Video tutorials     KnowledgeBase  
Formulae: Subset Functions - conditionality? - Visokio Forums
Formulae: Subset Functions - conditionality?
  •     davedunckley September 10, 2013 3:27AM
    Hi I am having a little trouble with Subset functions...

    What I would like to be able to do is to work out the total per charterer in the last 52 weeks.

    I have created a field called [islast52] that gives me a 'y' or 'n' depending. Then I am trying the following to get the results. However, I cant quite get it right. Some help would be great.

    SUBSET_SUM([Total USD Fixture], SUBSET([Charterer.fixture]),

    Thanks, Dave
  •        daniel September 10, 2013 4:44AM
    Try this:

    [Total USD Fixture],
    SUBSET([Total USD Fixture]), SUBSET([Total USD Fixture], "y")
  •     davedunckley September 10, 2013 5:01AM
    ah ha, that got me closer. Thanks loads:-

    [Total USD Fixture],
    INTERSECTION(SUBSET([Total USD Fixture]), SUBSET([IsLast52.fixture], "y")

  •        daniel September 10, 2013 5:12AM
    Oh sorry my bad, I only concentrated on the formula format not the fields themselves.
  •     davedunckley September 10, 2013 5:39AM
    Hi there,

    I am getting so close but still not right, this is giving me no data.

    Any one any ideas?

  •        daniel September 10, 2013 5:58AM
    One more field to replace!

    [Total USD Fixture],
    INTERSECTION(SUBSET([Charterer.fixture]), SUBSET([IsLast52.fixture], "y")
  •     davedunckley September 10, 2013 6:43AM
    Thanks so much for your help. Think I am getting there. However its still a little off.

    I am getting results for data that has a n in islast52 and and not getting data where there is a y.


  •     paola September 10, 2013 7:10AM
    You can use SUBSET2 to simplify the formula:

    SUBSET_SUM([Total USD Fixture],

    The formula will calculate total in field [Total USD Fixture], different for each [Charterer.fixture], under condition (IsLast52.fixture is "y"). Rest of the values will be ignored.

    If you wish to calculate total for "n", then you can use IF function to deal with 'other outcome'.
  •     davedunckley September 10, 2013 7:26AM
    Hi Paola,

    Thanks for your help, but getting the exact same issue, To explane, I would like to see a total of the [Total USD Fixture] per Charterer where the data was within the last 52 weeks {IsLast52 = y}

    So where last52 is no, no data should show in this formalua feild. However at present, I am getting data. Any idea why that might be?

  •     paola September 10, 2013 10:15AM
    It is not possible to answer that question without seeing your file, however attached here is a simple demo file that will demonstrate how the above formula works.
  •     davedunckley September 10, 2013 1:36PM
    You have the same issue in your file. There is data in the feild monthly total for y. This should not have nothing in it. as the last52 condition in not = y. So we should only be producting a total per month where last52 =y.

    Do you see what I mean?
  •        daniel September 11, 2013 4:04AM
    I see, the subset_sum formula works based on all filtered not just the row itself but it's easy to adjust, all you got to do is surround it with an IF function:

    SUBSET_SUM([Total USD Fixture], SUBSET2([Charterer.fixture],[IsLast52.fixture],[Charterer.fixture],"y")),

  •     davedunckley September 11, 2013 10:03AM
    Ah ha. Thats it.



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

Sign In Apply for Membership