Summing based on another modules field

MatthewWilcox
MatthewWilcox
in Modeling

Hi team,

 

Hopefully a straight forward one.

 

I have 2 lists. Campaign and Show ID

 

Multiple campaigns can have the same show however they are not within the same hierarchy.

 

I have a module INP01:Campaign Details that has the Show ID as a line item - not all campaigns have show IDs.

 

I have another module INP02:Episodes where the user inputs the number of episodes that a show will play in a given month. A show should have the same number of episodes in a month (called Unique Episodes) regardless of the campaign and users sometimes only update one campaign instead of all of them.

 

I am trying to create a formula to detect when the episodes in the month for any given campaign is not equal to the average for the show - this will detect if a campaign has not been updated.

 

in my head the formula is something along the lines of IF ISNOTBLANK('INP01: Campaign Details'.Show ID) THEN if Unique Episodes <> Unique Episodes[Sum:'INP01:Campaign Details'.'Show ID']/Unique Episodes[Count:'INP01:Campaign Details'.'Show ID'] then false else true else true 

 

say we had 3 campaigns to a show id and 4 episodes in a month the above formula would be 4 = 12/3 which is correct. If one was changed to 5 you would have (4 or 5 depending on campaign) <> 13/3 which would flag as needing correction.

 

The sum function does not seem to work. I thought lookup would also work but that is not the case.

 

Any help would be appreciated.

Tagged:

Answers

  • JaredDolich
    JaredDolich

    @MatthewWilcox 

    Great use case. In order to use the SUM function there has to be a relationship between the campaign and the show ID somewhere. The most optimal way would be to create a list that has all the combinations of campaigns and shows to reduce sparsity. Another way is to create a module that has both campaigns and events. Once the relationship is established the SUM function can be used and you can sum either on campaign or show depending on what the target module list is using.

  • MatthewWilcox
    MatthewWilcox

    Hi @JaredDolich ,

     

    Is my INP01 campaign details not enough to show the relationship between campaign and showID?

     

    Could I add the showID as a property of the campaign list and would that be sufficient?

     

    Thank you.

  • piyushbund77
    piyushbund77

    So... wait, I'm not sure I totally understand you.

    You want a measure that sums LBS and KGS... together?

    What if I select one group that requires LBS and another that requires KGS? I'm then looking at a completely invalid number (some combination of lbs and kgs). Like, if Group 1 requires lbs and Group 2 requires kgs, if I select both, I'm looking at a measure that says "720", which is composed of 500 lbs and 220 kgs?

    Something seems wrong here.

    2
    Reply
    Share
    ReportSaveFollow
     
     
     
    level 2
     
    OP
  • Misbah
    Misbah

    @MatthewWilcox 

     

    Could you help us understand what you are trying to achieve. I think there are too many things for us to assume.

    For example where does 13/3 calculation come from? Do users Input on a Time dimension or Is the Time in a Time period formatted line.

    It would be better if you could show us in excel what is the expected output based off of your inputs. See below for your reference.

    Screen Shot 2022-08-12 at 10.00.58.png

    Thanks,

    Misbah

    Miz Logix

  • jagadishdash
    jagadishdash

    Awesome use case.👏

     

    Got your use case, But Can you please more elaborate what you want to achieve? Cause there's a lot things to take it, as per your end result that can be finalize.

     

     

Categories