Counting Headcounts of Male Fulltime Employees Using IFTHEN Formula

agk729
agk729
in Modeling

Hello,

I am having trouble counting the number of headcounts using the IFTHEN formula in Anaplan.

My formula:

 IF    '01_PSFT・Excel data'.Contract = "Fulltime" AND '01_PSFT・Excel data'.Gender = "Male" THEN    1 ELSE    0

What I want to count: people to be categorized as both
・Contract column:Fulltime
・Gender column:Male

I created the list "06_Names" that has parents for each name, but I received the following message:

Invalid formula for '01_KPI Reporting'.'Parament Headcount-Male': Automatic sum of '01_PSFT・Excel data'.Contract over 06_Names hierarchy is not possible as 06_Names does not have a built-in top level

Please kindly let me know the way to fix formulas or the lists etc.

①Modules that I want to insert formula

image.png

②Modules as a data source of ①module

image.png

③The grid view of 06_Names list

image.png

Answers

  • devrathahuja
    devrathahuja

    Hi @agk729 ,
    Calculate the count in module 2 with the formula you have & then pull that line item into module 1. It'll auto sum it & show you the total value in module 2.

  • agk729
    agk729

    @devrathahuja
    Thank you for your reply.

    (Your reply) Calculate the count in module 2 with the formula you have & then pull that line item into module 1. It'll auto sum it & show you the total value in module 2.

    >Do you mean to put the formula below into the highlighted ② module?
    formula:

    IF '01_PSFT・Excel data'.Contract = "Fulltime" AND '01_PSFT・Excel data'.Gender = "Male"THEN 1ELSE 0

    Since this module is input data for the 01_KPI Reporting module, how can I count with the formula above?

    It seems Anaplan doesn't have a count formula like Excel. How can I count the items in Anaplan?

    Note: I merged two CSV files into one module.

    image.png

    I am deeply appreciate your support.

  • devrathahuja
    devrathahuja

    Hello @agk729 ,
    Yes exactly, create a new line item & put the formula in this one. The 1 in your formula will act as the count.

  • SriNitya
    SriNitya
    image.png
    image.png
  • seymatas1
    seymatas1

    Hi @agk729,

    In addition to suggestions of @SriNitya and @devrathahuja, I recommend this approach for scalability, performance, and best practices.

    1. Create two lists:

    List 1: Gender

    List items: Male, Female

    List 2: Employment Type

    List items: Contract, Part-time, Full-time

    2. Create a LOOKUP module:

    Line Items:

    Male, format: Gender list, Male is selected from the dropdown
    Full-time, format: Employment Type list, Full-time is selected from the dropdown

    3. Create a filter module or two Boolean line items in the existing module: Male? and Full-time?.

    Male?

    Formula:
    Gender = LOOKUP Module. Gender

    Full-time?

    Formula:

    Employment Type = LOOKUP Module. Employment Type

    Count

    Formula:

    If Male? AND Full-time? THEN 1 ELSE 0

    Avoid hardcoding, and consider scalability and performance:

    Text is not a memory-friendly data type; minimize the use of it as much as possible. If statements with hard-coded text will slow down your model.

    Hard coding is always a pain when it comes to maintenance. A typo can break down your formula.

    At some point, there will be a requirement to count, female - full-time, male-contract, etc. Your Employment Type list will also grow: Hourly, Intern, etc. This solution is more scalable. It is just a matter of adding another list item, Lookup or Boolean.

    I hope this helps.
    Seyma 🌷🙂

  • agk729
    agk729

    Thank you for your continued support.

    I have tried all the suggested approaches, but I have not succeeded yet.

    Approach 1 & 2: @devrathahuja @SriNitya
    When I entered the formula:

    IF Gender = "Male" AND Contract = "Fulltime"THEN 1 ELSE 0

    The following error message appears:

    The formula for '01_PSFT・Excel data'.count is invalid:Datatypes do not match for '=' function: LIST:02_Gender, TEXT



    Approach①②

    image.png

    Approach 3: @seymatas1
    I created two lists:

    1. Gender
    2. Employment Type

    I created both lists and made line items as shown in the screenshot below.

    When I tried to create two Boolean line items in the existing module, the formulas were not authorized. The formulas were:

    "Male?": Gender = LOOKUP Module.Gender"Fulltime?": Employment Type = LOOKUP Module.Employment Type

    The following error message appears:

    The formula for '01_PSFT・Excel data'.Fulltime? is invalid:Fulltime? = Employment Type = LOOKUP Module.Employment TypeEmployment Type is not a recognized line item or list member


    Approach ③

    image.png


    FYI:
    List 1 Gender

    Gender list.png

    List 2

    image.png

    Although this thread takes a bit time, I deeply appreciate your support!

  • devrathahuja
    devrathahuja

    Hi @agk729 ,
    If you're going for approach 1/2 use name(gender)="Male" and same for the other line item as we need to convert the list format to a text format first.

    I also suggest using a more dynamic approach (approach 3) as suggested by @SeymaTas, can you also share the lookup module? The lookup module should not have any dimension & the line item format should be the same list - Gender & Employment type.

    It'll then match both the list members & give you a true for that boolean. Here you can directly refer to the line item instead of using name(gender), so the formula will be gender line item = male line item in lookup module.

    Hope this helps! :)

  • agk729
    agk729


    Thank you for your reply.

    I attempted to use the LOOKUP module for the "Male?" line item with the following formula:

    IF LOOKUP(Gender) = "Male" THEN 1 ELSE 0

    However, I encountered an error. The formula for '01_PSFT・Excel data'.Male? is invalid:

    Male? = IF    LOOKUP(Gender) = "Male"THEN    1ELSE    0

    The error message states that 'LOOKUP' is not a recognized function name.

    The formula for '01_PSFT・Excel data'.Male? is invalid:
    Male? = IF
    LOOKUP(Gender) = "Male"
    THEN
    1
    ELSE
    0
    'LOOKUP' is not a recognized function name

    Screenshot of the module which I want to insert the formula:

    image.png
  • devrathahuja
    devrathahuja

    Hi @agk729 ,
    You don't need to use lookup in the formula. You need a SYS Lookup module with line items for Gender & Contract Type.

    image.png


    The formula for the Male Boolean line item then needs to be Gender (in the module 1 - 01_PSFT…) = Gender (In the SYS Lookup Module)

    Same for the contract type.

  • agk729
    agk729

    @devrathahuja
    Thank you for very much for the instruction.

    I have created the LOOKUP module as shown in the screenshot below and successfully entered the formula.

    However, even though the formulas were installed, the booleans do not appear in the "Male?" and "Female?" lines. I am unable to understand why the "Fulltime?" line correctly shows the result.

    In this case, how should I change the formulas ? Or are the lists and modules have problems ?

    ①Grid view


    ②Lookup module

    image.png

    ③1_PSFT・Excel data Module

    image.png
  • devrathahuja
    devrathahuja

    Hi @agk729 ,
    Have you selected "Male" in the lookup module line item? It'll work exactly in the same manner as the Part time vs full time employee boolean :)

  • agk729
    agk729
    edited February 28

    @devrathahuja @seymatas1
    Thank you for your assistance !

    When I added each items in the SYS LOOKUP Module as you mentioned, the formula correctly worked on 01_PSFT・Excel data module.

    It seems I have to crete lines for each item on the Lookup module in Anaplan...

    image.png
    image.png
  • devrathahuja
    devrathahuja

    @agk729 - Yes, that is correct, 1 for each item you want to flag.

    Alternatively you can store the Male/Female & Parttime/Fulltime boolean flags in their SYS modules too so you can in future refer whereever requried.

Categories