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.
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'.
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?
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.
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.
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: