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]),
subset([IsLast52.fixture],"y","=")))

Thanks, Dave

daniel · September 10, 2013 4:44AM

Try this:

SUBSET_SUM(
[Total USD Fixture],
INTERSECTION(
SUBSET([Total USD Fixture]), SUBSET([Total USD Fixture], "y")
)
)

davedunckley · September 10, 2013 5:01AM

ah ha, that got me closer. Thanks loads:-

SUBSET_SUM(
[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?

Dave

daniel · September 10, 2013 5:58AM

One more field to replace!

SUBSET_SUM(
[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.

?

Dave

paola · September 10, 2013 7:10AM

You can use SUBSET2 to simplify the formula:

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

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?

Dave

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:

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

davedunckley · September 11, 2013 10:03AM

Ah ha. Thats it.

Thanks

Welcome!

Categories

Ideas Parade

Tagged