About Jsdeloria21

Jsdeloria21 · ‎07-09-2020

Hi, As mentioned I changed the format to Y-M and custom format YYYY-MM and this is what I got. From Expected 2-digit month number in date/period. The prompt now shows 4-digit year in date/period. Does this also explain the sudden increase in the figures after it is imported in AIC PNL? Regards,

Jsdeloria21 · ‎07-09-2020

Hi, We are using the same time range however there is a Dimension in the IC PNL which is not needed in the AIC PNL that we removed. See below. This Dimension is only present in the IC PNL but not on the target AIC PNL. Is this what is causing this problem? We cannot include this column because it is not the same version as AIC PNL. I hope that makes sense. So in the mapping part we removed it. \\ Any ideas on importing the data to a Target module without this version present? Regards,

Jsdeloria21 · ‎07-09-2020

Hi Misbah, Appreciate the response. They do have the same timescale, however, in the IC PNL module we have a dimension which we do not need to use in the AIC PNL. It is the process flow dimension. Is that why I cannot use the match names? Highlighted below is the dimension I am talking about. This is not present on the target module since AIC PNL will have a different version. Is this why I cannot use the Match name option? Because the error with the match names it prompts on looking for that dimension. Regards,

Jsdeloria21 · ‎07-09-2020

Hi, Appreciate the quick response. I tried Match Names earlier and it popped up and error. So i tried the custom period MMM YY. same error comes up Regards,

Jsdeloria21 · ‎07-09-2020

Hi Guys, This might be simple but I do not get why it is having an error. So I have 2 identical modules. I have IC PNL and AIC PNL. They are both identical in terms of dimension and line items. So what I am trying to do is import IC PNL to AIC PNL that simple. Whatever numbers is in the IC PNL should be the same as the numbers in AIC PNL. I am getting this error, see below: This is the current settings in TIME that I have. I tried the custom format as well as match names and it is still not working. And also the figures are doubled up. ANy ideas on this issue? Regards,

Jsdeloria21 · ‎06-30-2020

Hi Andrei, Thank you so much for answering the queries yesterday I was able to build the calculation. I would like to clarify some stuff if it alright. Particularly on this table. I can assume that this table will be the first approach that a user/client will have to set before the calculation starts. Now as you can see below you set Month 1 - Jan 19, in my case I started with Month 1 - Jan 18 and it calculated the Oct 18-Dec 18. Now I have this questions to ask. 1. Let us say that client has 24 mos spread on the Counter dimension and I set Month 1 - Jan 18 so meaning it should cover Jan 18 - Dec 2020. If ever I have a computation on Jan 21 which is not covered on this table. Does this mean there will be no computation present on the Payment Module since it is not mapped on the Counter Time Mapping module? 2. Also this one is related to the Number 1 question, in my actual Run I have a value to be computed on Source Module dated Sep 16, since it is not mapped on this table does this also mean that there will be no computation present on the Payment Module? 3. I am assuming that in this module Counter Time Mapping you manually input the Months based on the number of spread Month 1 - 24. Is there a way to automate the mapping? Meaning this table will map only the Months on the Source module with values in it? Since I already build the solution you presented I am currently applying the scenario in the actual model itself. Appreciate your response. And again thank you. Regards,

Jsdeloria21 · ‎06-29-2020

Hi Andrei, Thank you for the response it really helped a lot. Here is that I have so far. This is what my Principal Staging looks like now. As you can see on the Percent Counter Time Lookup line Item I only have the "Month 10" while on your example it counts Month 1... Month 2.. etc. Did I do something wrong or am I missing something? Regards,

Jsdeloria21 · ‎06-29-2020

Hi Andrei, Sorry for disturbing you. I was able to replicate the solution that you sent me. See below: I only have 2 missing line items that I need to clarify with you. What is the complete formula for Cumulate count and in the Counter Time Month line item, what is Counter time mapping.Month, is it a module or a list? I think I am missing that part as well. Plus the question that I have with Time - Counter Mapping table. Appreciate your response Regards,

Jsdeloria21 · ‎06-29-2020

Hi Andre, My apologies again I think I was wrong on analyzing the screenshots. I just want to clarify my first reply to you. Is this a module where Row - Time and Counter Time = Counter Dimension ? If so, is it possible to view the formula of this module so I can compare it with what the client. Thanks

Jsdeloria21 · ‎06-29-2020

Hi Andre, My apologies for the late reply on your response. And I want to say thank you for taking time on answering the query. I would like to ask something about your solution particularly on the table below. I can see that the Counter Time column on the list was already set to Month 1 - Jan 19. I already had the Column Counter time no ready. Just want to ask, did you set the Month 1 as Jan 19 on the list.? I mean did you assign it manually on this table below? What if there is a value on "Principal" which is Sep 16, does this mean Sep 16 should be tagged as Month 1? I am currently applying your solution to the set up that we have already. Just need some clarifications on this. Hope to hear from you Regards,

Jsdeloria21 · ‎06-26-2020

Hi Anikdas, Base on the template of the client they have Total/Average and a Total on the table. But if there is a way that you can compute it separate I am open to that option as well. Thanks.

Jsdeloria21 · ‎06-26-2020

1. i assume Flr Area, units, SP(VAT -ex) are line items and Unit Mix is a dimension of a module. - Yes. 2. Total/Average (Flr Area) = (Flr Area (Studio)*Unit(Studio)) + (Flr Area (1 BR) *Unit (1BR))+ (Flr Area(2BR)*Unit(2BR)) + (Flr Area (3BR))*Unit(3BR)) + Flr Area(4BR) * Unit *4BR)) /Unit(Total/Average - is it a formula written on Flr Area line item...? - It is written on the Total/Average - Flr Area column. 3. Total/Average (SP(VAT -ex))=(SP VAT-ex (Studio)*Unit(Studio)) + (SP VAT-ex (1 BR) *Unit (1BR))+ (SP VAT-ex(2BR)*Unit(2BR)) + (SP VAT-ex (3BR))*Unit(3BR)) + SP VAT-ex (4BR) * Unit *4BR)) /Unit(Total/Average is it a formula written on SP(VAT -ex) line item...? - It is written on the Total/Average SP (Vat - ex) Column 4. I tried to use Flr Area[select:Studio]*Units[select:Studio] and I got the Circular reference in which module are you writing this formula..? - on the same module, Under Flr Area - Total/Average. I did a IF item(Unit Mix) = Unit Mix.Total Average then Flr Area[select:Studio]*Units[select:Studio] and what is the line item name...?

Jsdeloria21 · ‎06-26-2020

Hi All, Please see attachment for details. My apologies my post encountered an error so I have to attach the details Regards,

Jsdeloria21 · ‎06-26-2020

Hi All, I would like to ask for some insights with a Circular Reference Issue. Here are the details So we have a dimension Unit Mix, where the TOTAL is the Top Level and Parking and Total/ Average is the child. And under Total/Average are child member 1 BR, 2 BR, 3 BR, 4 BR. ( I hope i was able to illustrate the dimension properly). The clients requirement is to calculate the Total/Average of the 4 Unit Mix without the Parking as they separate the calculation for this. What we are trying to calculate is the Total/Average for Flr Area and SP (Vat-ex). Formula Total/Average (Flr Area) = (Flr Area (Studio)*Unit(Studio)) + (Flr Area (1 BR) *Unit (1BR))+ (Flr Area(2BR)*Unit(2BR)) + (Flr Area (3BR))*Unit(3BR)) + Flr Area(4BR) * Unit *4BR)) /Unit(Total/Average Total/Average (SP(VAT -ex))=(SP VAT-ex (Studio)*Unit(Studio)) + (SP VAT-ex (1 BR) *Unit (1BR))+ (SP VAT-ex(2BR)*Unit(2BR)) + (SP VAT-ex (3BR))*Unit(3BR)) + SP VAT-ex (4BR) * Unit *4BR)) /Unit(Total/Average I tried to use Flr Area[select:Studio]*Units[select:Studio] and I got the Circular reference. I even tried to create a duplicate version of the module and refer it to that (since it worked in the past) but still getting the same error Any ideas? Any help would be appreaciated Regards,

Jsdeloria21 · ‎06-22-2020

Hi Misbah, Thank you for the response. I kinda figured that this is going to use the LOOKUP function. Is it ok if you can show me how to do this approach? Regards,

Jsdeloria21 · ‎06-22-2020

Hi, PLease see details in the attachment. Regards,

Jsdeloria21 · ‎06-09-2020

Hi Kanishq17, With some new information from client with regards to this query, I have some questions on this 1. On the screen shot below, the module below with filter tower line item, is this a module with a List on the rows pertaining to a fix number of towers? 2. in the formula, ISBLANK(inp.No of buildings) or Towers.number tower <=inp.Buildings val. Where is Towers.number tower present? In the list of in the module? Appreciate the response I am currently re-building the current design base on this approach Regards,

Jsdeloria21 · ‎06-07-2020

Hi Misbah, Apologies for inquiring the same issue, I was able to somehow replicate the solution you provided and presented it to the client. However, upon our discussion some aspect suddenly changed. The engine as the client referred to it is actually not time bounded. The client made a mistake by putting the Calculation engine in the report section (where is it time bounded) and we made a mistake by assuming that it is time bounded. This is the new illustration base on the new discussion: This is the new look of the Engine, no time dimension present just a fix table. However, the counter line item will be inputted depending on the spread. In this case, I created 24 months spread. In their report it was 48 months but client also mentioned that it can reach 60 months. This is suppose to be the result. Can I still use that same approach that you presented before? Also, is there a way that we can create a list where the list members can be determine by the value inputted by the user (this is pertaining to the Counter line item)? Appreciate the help on this Regards,

Jsdeloria21 · ‎06-03-2020

Hi Misbah, I am trying to use the approach you did regarding this concern. I just have a few questions if you don't mind. 1. On the below list (which i have done already), how did you determine which month is assigned to the mapping line item? 2. On the table below, i notice they have subsidiary view. What time range did you use on this? Apologies for the late reply, I am trying to fit your approach to the current model we have. hope to hear from you. Thanks in advance Regards,

Jsdeloria21 · ‎06-01-2020

Hi Misbah, Appreciate the response. To answer the question what happens when it crosses the month of June. It will still multiply it to whatever the values is present on the line item. The line item 1 is also calculated in their report sheet. I will try to check your approach in the model. Thanks again, Regards,

Jsdeloria21 · ‎06-01-2020

Hi Guys, I would like to ask help on this issue that I have never encountered before. In the clients excel sheet they are using a SUMPRODUCT formula. The Logic is multiplying the Start of the month (which is the starting month for their calculation) to the End of the month of the calculation engine but backwards. Let me demonstrate the action: Below is the 1st line item (which is a list) with values from Jan 16-Dec 16. 2nd Line item (which consists of the same list as above) has percentage but the value starts at the Month of Dec 16 backwards. Also, there is a line above labeled Month Spread. This is sort of their tagging per month on what month is the 1st month to start their calculation backwards. Base on their formula, the multiply the Value 1.Jan 16(which is 2) to the Payment Scheme 1.Dec 16 and so on. See below as you can see in the table above, Value 1(Jan 16) * Payment Scheme 1 (Dec 16) for the month of Jan 16, Value 1(Feb 16)*Payment Scheme 1(Nov 16)+ the value in JAN 16 and so on. Hope you can understand the illustration that I showed you since I cannot really post the actual report for you. Appreciate the help here. Regards,

Jsdeloria21 · ‎05-27-2020

Hi Rob, to be honest in the sample report that the client sent us. The No. of bldgs they inputted on their Assumptions was 16. That is same sample I gave you. But let us say maximum they have is the 2nd example 4 bldgs. The client has not confirmed the maximum towers as of posting. Appreciate the response Regards,

Jsdeloria21 · ‎05-27-2020

Hi Guys, I need some assistance on what approach to use here. Below is the description. The clients report consists on a table where they input the No. of Bldgs. and on another report those numbers of bldgs will be spread out. The result on the Target module will be Another example will be and the result on the target module will be So its like a dynamic table depending on the input of the client. Is this like a numbered list because I never encountered something like this before. Appreciate the assistance Regards,

Jsdeloria21 · ‎05-27-2020

Hi Sorna, Appreciate the response on the issue, we did the option 1 to change the format and show the value with decimal place. However, the client would want to display it as a whole number since according to the it is a OPSTAT report. Is there a way that ANAPLAN can compute the values in decimal with decimal and still display it as a whole number? Or do we just tell the client that ANAPLAN computes values as it is? Regards,

Jsdeloria21 · ‎05-27-2020

Hi Guys, I just want to ask something. Our client has an excel report template, its more like Operating Statistics type of report. It basically counts values per month and it is usually referenced to another cell in their report. After checking the issue sent to us I notice one 1 line item (actually it is an entire year) where the values does not match and that is where my inquiry comes from. See below Report as seen in their excel sheet: After expanding the excel sheet in 5 decimal places. It shows below In ANAPLAN this is what we captured: As you can see above the total is different and this may have an impact on the OPSTAT report that they have. Is there a way we can capture the same value in ANAPLAN? Appreciate in advance. Regards,

Jsdeloria21 · ‎05-19-2020

Hi Guys, This might be simple to some but I am having problem with the result here. See below Module 1 is a module where users input the start of Data of Projection, this is their input module. Module 2 on the other hand is a Line Item named Value and a Time Dimension. So if a user input a Date (Time Period format) April 2020 on Module 1 it will then look to the Module 2 and return the value with the same Date (Time Period) from Module 1 as seen below. I have 2 examples here hope it makes sense. Any ideas on this let me know and thank you in advance. Regards.

Jsdeloria21 · ‎05-17-2020

Hi Guys, So I have an issue with my IF formula, initially I was able to get the logic of this but the client decided to add a Contract Status (Full Cancellation). After that my count seems to be out in some months. So I need to adjust the logic but for some reason I am getting the correct results in some months but after inserting the Full Cancellation logic it is not counting correctly. Sample below. Contract Status Book Date Commitment Date Formula in Excel: Initially this is formula If(A=”Cancelled”,IF(B=<>”,”Cancelled”,IF(C<>””,”Back Out”,”Fall Out”)),IF(OR(A=”Cancelled with Re-Contract”,A=”Recontruct”),A)) Since adding a new status Full Cancellation my logic is off in some months. If(A=”Cancelled” or A=”Full Cancellation”,IF(B=<>”,”Cancelled”,IF(C<>””,”Back Out”,”Fall Out”)),IF(OR(A=”Cancelled with Re-Contract”,A=”Recontruct”),A)) Is there a way to simplify this in my ANAPLAN formula? Below is my ANAPLAN formula (I know it is too much IF statement which is not advisable). IF Contract Status = 'Contract Status (V)'.Cancelled OR Contract Status = 'Contract Status (V)'.Full Cancellation AND ISBLANK(Book Date) AND ISBLANK(Commitment Date) THEN 'Contract Status (V)'.Fall Out ELSE IF Contract Status = 'Contract Status (V)'.Cancelled AND ISBLANK(Book Date) AND ISNOTBLANK(Commitment Date) THEN 'Contract Status (V)'.Back Out ELSE IF Contract Status = 'Contract Status (V)'.Full Cancellation THEN 'Contract Status (V)'.Cancelled ELSE IF Contract Status = 'Contract Status (V)'.Recontract THEN BLANK ELSE IF Contract Status = 'Contract Status (V)'.Booked THEN Contract Status ELSE IF Contract Status = 'Contract Status (V)'.Fully Paid THEN Contract Status ELSE IF Contract Status = 'Contract Status (V)'.Fully Paid with OC THEN Contract Status ELSE IF Contract Status = 'Contract Status (V)'.Reserved THEN Contract Status ELSE IF Contract Status = 'Contract Status (V)'.With Downpayment THEN Contract Status ELSE Contract Status Appreciate if there is a much simplified formula for this. Regards,

Jsdeloria21 · ‎04-11-2020

Hi Guys, I would like to ask something regarding a formula I have been working on. See the table below. Price Escalation formula = Input Avg Selling Price = The Jan 13 value is constant referenced to 2.3 which is in another module. While the succeeding months starting Feb 13 to Mar 14 the formula is Avg Selling Price*(1+Price Escalation). I tried the formula IF Price Escalation = 0 then Avg selling Price else Average Selling Price*Price Escalation. I also tried IF Previous(Avg Selling Price)=0 then Avg Selling Price else Avg Selling Price*Price Escalation. I still do not have the right calculation on my Line Item. Need a little input on this. Appreciate it. Regards,

Jsdeloria21 · ‎02-19-2020

Hi Jared, Thank you this was very helpful and I was able to solve it.

Jsdeloria21 · ‎02-17-2020

Hi Guys, I have a simple one to ask. I am loading a Period with a Text format to a Year and Month with a Number format. Example: I loaded a date of Jan 13 to a Period column. Then I need to convert it to Month 1 and Year 2013. I tried loading it as Time period which is the right way but for some reason it is not loading successfully. See below Appreciate the help. Regards,

Online Status	Offline
Date Last Visited	‎07-09-2020 12:30 PM