Comparing the Total for each element of a list, not the total value of the list


Hello everybody,

I've been struggling with a recent dev especially on a specific case, which i hope some you would enlighten me ^^

To Make it short, I have two list :

  • G3 Retail (belong to a hierarchy (G2 & G1)
  • G3 Destination Store (which is a parallel list of the G3 Retail list & is having a top level)

Here is a picture below to understand the idea of the development

For the sake of the explanation i rename here the G3 Retail list as => Starting Store

and the G3 Destination Store as => Destination Store

The idea behind the dev is as follow, if an item of the Starting Store list equals 100% then the calculation is done, else nothing is done.

What i did here here this

But the problem is that it's working only if i just do imputations for one Starting Store, because the [SELECT : G3 Destination Store.Total] will equal 100% hence 1.

However if i do imputation for more starting store, then the [SELECT : G3 Destination Store.Total] will add the values hence not woking anymore

I hope i was clear enough !!

Thank you very much

Best Answer

  • AjayM
    Answer ✓

    You are right about (1). It has to be by both Starting Store and Destination Store, to allow input. However, a recommendation to keep it clean is, to separate input lineitems into a different module, which is dimensioned same as input lineitem.

    (2) - If you have to do multiplication for a valid combination of starting store and destination store, then yes, you need both those dimensions in the highlighted LI.

    (3) - Yes, you are validating each starting store and sub-division combination to have a 100% input across destination stores. This doesn't need destination store as a dimension, because you validate at TOTAL destination store level.

    Please try making these changes and test if it satisfies your need.


  • AjayM
    edited March 2023

    Hi @HugoVolpi ,

    I have few questions with the requirement, if you can please share:

    1. In the rows, you have another dimension next to 'Starting Store', lets call it ProductType. Is your requirement to allow calculations if for each Starting Store, the sum of input against each ProductType item is 100% or against all ProductTypes is 100%?
    2. What is the dimension against which you have written the aforementioned formula? I believe it's by Starting Store alone and not along with ProductType. Please confirm
    3. The failure case you mentioned above with a screenshot, I assume it's for same Starting Store but against multiple ProductTypes. If that's not the case, if it is across multiple starting stores and if your formula is dimensioned by Starting Store, then the total of 100% for each starting store does get satisfied (as I see a gap of 5 rows with 0% for ProductType list with 6 items).
  • HugoVolpi
    edited March 2023

    HI @AjayM,

    First of all thank you for your well appreciated detail feedback on the matter.

    1) As you mention, the condition needs to be validated if the sum of input against each ProductType item is 100%.

    2) The formula is having the ProductType in its AppliesTo (P1 Sub-division) and it also have the Destination Store

    In my mind i wanted to do something like "if your Starting Store is sending all it's 100% in the Destination Store then the condition is met", which is why i thought i had to put the Destination Store list in the AppliesTo

    3) My screenshot was not clear enough sorry, i took a screen (see below) and even in that case my formula doesn't work because i took the total of my Destination List.

    I don't know how to put into formula the need " If all the 100% of a Starting Store gets correctly applied in one (or more) Destination Stores then it validates the condition"

    In the case here (screenshot below), it's doesn't work anymore because of

    Hope it helps, tell me if i was not enough

  • AjayM
    edited March 2023

    Thanks for sharing more details @HugoVolpi .

    I don't think you need Destination store in the calculation lineitem, because you are validating 100% for Total of destination store dimension (not by each individual destination store item).

    As per (1) and my understanding, for each starting store and P1 Sub-division item combination, the requirement is meeting 100% [Ex: OTS Merge%[Select: Destination]=1]

    As per (2), you should ideally be calculating against Starting Store and P1 Sub-division

    As per (3), two different starting stores are meeting 100% across all destination stores. If you correct your dimensionality to remove desitnation store and replace it with starting store, this should work fine.

    Please let me know if we are not aligned.


  • HugoVolpi
    edited March 2023

    I think i understand what you mean because overall i got this , and to add some information that i didn't gave Earlier :

    1) the LI % OTS MERGE is the imput the % so for that i guess i have no choice but to put both Starting Store and Destination Store to make the connection

    2) I have a second LI : "%OTS MERGE & OTS Starting Store" which basically multiplicates my LI 1) * another LI that i highlighted in yellow (but this LI (highlighted in yellow) was initially calculated under the Starting Store List but now i want to get this value * the %OTS MERGE under the store i gave imput under the destination store)

    3) The third line item was to make the condition i explained earlier, if not 100 % then...

    You are saying that i need to apply the condition on the starting store and not the destination Store (Total = 1) ?

    Sorry for taking this time..

    Thank you very much

  • Hi @AjayM, it was indeed the issue here, thank you for your support !