Issue with Formula

CommunityMember82499
CommunityMember82499
in Modeling

Hi Experts,

 

I have 2 issues with my formula,

 

1. I have written a formula to line item "Territory Code as Number" = IF Active Assignment? AND LEFT(Territory Code, 1) = "G" THEN VALUE(RIGHT(Territory Code, 2)) ELSE VALUE(Territory Code). I have considered the below things to the line item. Format = Number Time Scale = Not Applicable Time Range = Not applicable Version = Not Applicable Summary = Max It is working fine for the territories which are having alphanumeric ex: G71,G72,G73 etc.... but where as the same formula is not working for the single territory which is having only alphabetic i.e. GNC... To except GNC the formula is working absolutely fine, So,could someone help me how to evaluate the function for GNC also.

 

2. I have a query with the same line item formula, based on the Summary : Max, the line item is picking the maximum value of the territory and there is a one more line item called Active Assignment? with boolean format to pick the active year i.e 2019 current year of the territories, it is giving perfect result, but for the few of the managers the active assignment territory is not the max value, that means, I have an ABC manager under him there are some territories like 100,200,300,400 for 2018 year and for the 2019 my max value of territory is 200, instead of 200 it is picking 400 even-though I have written the If active assignment condition in my formula but still the formula is working fine here also because the If Active Assignment condition is picking the max value of the complete line item. But my formula should work to pick the max value for 2019 year to the specific manager.because the max value of the 400 to ABC manager is not available in the year 2019 but whereas same 400 max value is available to some other manager in 2019. So it is picking the max value for the active assignment i.e. 2019. but I want it to the specific manager who is not having 400 value. so it should pick the max value of the particular manager to the active year.

 

Thanks
Sasikanth

Tagged:

Answers

  • rob_marshall
    rob_marshall

    @CommunityMember82499 

     

    For point one, what value do you want to give the Territory Code if the code is all letters?  I guess my real question, what are you hoping to accomplish by putting a max on that line item?

     

    For point 2, utilize lookup to the current year using the SYS Time module where you create a line item that is referencing the current year.  Line item: Year, format is Time Period (Year),

     

    2019-05-28_08-44-41.png

     

    2019-05-28_08-44-53.png

     

    Hope this helps,

     

    Rob

     

    . 

     

     

     

  • alexpavel
    alexpavel

    Hi Sasikanth, 

     

    for issue 2: 

    Just create another line item with a formula 

    If Active Assigment? then value line item else 0

    Set this line item with MAX as summary. 

    this way you will have the Max 200 only for the Active assignment territories. 

     

Categories