Visokio website     Downloads     Video tutorials     KnowledgeBase  
Formulae: SUBSET - testing the same field for different values? - Visokio Forums
Formulae: SUBSET - testing the same field for different values?
  • nicci113 May 22, 2013 9:49AM
    Hi There, Can somebody please help?

    If I want to select [NewField2] and look for values 'CARBRAND' then pick corresponding value from [Spend] and ADD that to where [Newfield2] value is 'PUREBRAND' and the value from [Media..] Highlighted in attachment
    Attachments
    Capture.PNG 9K
  • 6 Comments
  • nicci113 May 22, 2013 10:06AM
    IF(and([New field2]="carBrand",[Spend]) +
    [New field2]="PureBrand",[Media Spend Car])

    This is the formula I though but its not working any suggestion will be greatly appreciated???
  • nicci113 May 22, 2013 10:36AM
    Do I ave to use IF else or something else, it seems to do something but not in a way I want... please guide me, its my final batch
  •     tjbate May 22, 2013 1:57PM
    Nicci: Your formulas need to evaluate row by row. If you are are testing for [Newfield2]='CARBRAND' then you cannot also test for [Newfield2]='PUREBRAND' ...both things can never be true for the same row, unless you want to tokenise the field and permit multiple values per cell...?

    SUBSET functions can process groups of rows with the same value in a given field, but in your data set, you have logic trying to group more than one value for [Newfield2].

    I think we need to do some more pre-processing to the data set such that you do not need to test [Newfield2] twice for different values?
  • nicci113 May 22, 2013 5:26PM
    I have created few extra column to avoid such thing and guys in work seems ok so far.. This callc actually shows them overall comparison and really want this in the report.
  •     paola May 23, 2013 7:17AM
    Your Field organiser formula is adding values of 2 cells, from fields [Total spend] and [Media spend] in the same row. A 'vertical' sum is performed when you aggregate data.

    If you wish to have different total for each data subset, depending on the value of another field, e.g. to calculate sum for each data Category, then you need to use subset formulas, such as:

    SUBSET_SUM([Test], SUBSET([Category]))

    The sum of the [Test] field, for the data subset with the same [Category] field value as the record being evaluated.
  •     paola May 23, 2013 10:28AM
    The formula needed here was
    IF([New field2]="PureBrand",
    SUBSET_SUM ([Spend],SUBSET([New field2],"CarBrand"))+
    SUBSET_SUM ([Media Spend Car],SUBSET([New field2],"PureBrand")),
    null)

Welcome!

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

Sign In Apply for Membership