Summing based on another modules field

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.

Accepted answers

All comments

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

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

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.

ReportSaveFollow

level 2

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

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.

Quick Links

Can you help?

Unanswered questions

Batch Script
Hello team, I am logging into Anaplan through SSO. I would like to create a batch script to import a file into Anaplan. For the Spoke model, I would like to run an process via a batch script to import data from the Data Hub. Can anyone provide me script for both the condition.
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…