nested Sum if

Archanatiwari
Archanatiwari
in Modeling

Hi, 

I need to get total of selected roles occurrence which can fall in three different categories. For example, among all the users , I need to count how many users have role of ADM, and then  if they had the same role 3 months ago also- If yes it will be counted in No change list ( Category 1) and if their role is changed, It will be counted into new role (category 2 ) and third is the new  users who have no history 3 months ago ( Category 3). Following is the screen shot 

Archanatiwari_2-1650864553669.png

 

Following is the data table 

Archanatiwari_1-1650864469461.png

Can some one please guide me how can I achieve it. I tried using Sum if function  when role was not changed and it gives me following error 

Archanatiwari_4-1650864760967.png

 

Thanks 

 

 

 

Tagged:

Answers

  • ManjunathKN
    ManjunathKN

    Hi 

    In Anaplan, Syntax for sum formula is 

    Soruce.lineitem[sum : Mapping]
    https://help.anaplan.com/fa7ab44f-c31c-4928-88b3-9fe28ca8a774-SUM 

    for if else,

    if "condition returns boolean" then 1 else 0. instead of 1 and 0, you can input logics to return desired value.

    for nested sum if, please try to code in below way.

    If "boolean condition" then source.lineitem[sum : mapping] else if "another boolean condition" then ...so on. - this is for sequential nested if 

    if "boolean condition" then if "another boolean condition" then 1 else 0 else 0.. - this is for multiple condition nested if

    It is better to avoid nested if logics in anaplan unless it is required to achieve the result.

    Thanks,
    Manjunath

  • Archanatiwari
    Archanatiwari

    Thanks for replying Manjunath, I was able to achieve the solution using COUNT function in place of Sum. 

Categories