I have 3 modules. First module has list with yearly values. second module has line items that convert the yearly data to quarterly. I want to convert quarterly data monthly in third module. The quarterly module has override capabilities so if user select set annual to 0, then the total for that year will be 0. If user select override for Q2, then the updated value of Q2 will be carried down to Q3 & Q4. First module is year timescale, second is also year timescale and third module is month timescale.
@Amittiwari0507 - I used parent(item(time)) <> previous(parent(item(time))) It checks if the FY has changed in that case
Check if this helps:
Annual=$0, all quarters and all months become 0. Q2 override entered then Q2, Q3, Q4 months reflect override value/3months. If no overrides, monthly values = (Annual/4 Quarters)/3months
Hi @Amittiwari0507, What is the question here?
Hi @Amittiwari0507 , Is there a reason you're using line items for Quarters instead of native timescale? If you change the set up to using the native timescale you can use quartervalue(source line) to get the value at month level. In the current set up, you'd need a mapping for Quarter-Month and can then use lookup to get the values, however lookup on time has a very bad impact on the performance so I'd suggest you move the set up to native quarters and use quarter value. Hope this helps! :)
In my monthly module, i want the quarterly breakdown in months. Example: Q1 = 25 so monthly would be 25/3 = 8.33 however, my Q2 value has been overridden to 36 so monthly module should show values as 36/3 = 12. The same would be for Q3 & Q4 unless there is an override for either Q3 or Q4. In case user has selected override Annual, then the data for Quarterly module and Monthly module would be 0 regardless of any other overrides.
How do i move the setup to native Quarter timescale? Do i need to change the Timescale in blueprint mode from Yearly to Quarterly?
@Amittiwari0507 - Yes, enable the quarter totals-
And then change the time scale in the module-
You'd find quarter here once quarter totals are enabled
It's still not working. If i change the timescale of module to Quarterly, the formula in the line items are working fine. However, if i change the timescale of the line items to Quarterly, all my calculations result are blank. Secondly, in my Monthly module the values returned are all 0's.
@Amittiwari0507 can you share your blueprint view? You don't need individual quarter line items but just 1 line item for override, override to zero etc & utilise the native quarter functions. Here's an example on how it'd work-
Here is the blueprint for Quarterly Module
@Amittiwari0507 - Remove the Q1 - Q4 line items, you have the quarter timescale to take care of that. Anywhere you have a Q1 override, Q2 override or Q1,Q2 value it will just be a single override & a single value line item across quarters. I've shared the example in earlier screenshot on how the source & target would look. Check once how previous function would help here to refer to earlier quarters.
Can you please share the blueprint mode of your module?
@Amittiwari0507 -
what is the formula for Final line item?
Here we will check if there is any override in any quarter first? If there is then if there is an override in that quarter then we take that value else we take the previous quarter's value
What about Annual Override Boolean? Basically the final line item should be: If Annual Override?(boolean true) then 0 else the above formula that you shared
@Amittiwari0507 - Yes correct, you'd have to use if Yearvalue(annual override) then…. Since annual override will be at year level
It is not functioning as expected. This reset the value of all FY to 0
@Amittiwari0507 - Yes correct, we need to modify the quarter override here to check for each year separately by adding a condition for 1st quarter-
Did you use
QUARTER(ITEM(Time)) = 1
for 1st Quarter
I am trying to create a Net Debt / EBITDA formula at the bottom of one of our P&L income statements where, i have tried the MOVINGSUM function. However this does not work for summary line items which EBITDA is. What i would like is to be able to sum the previous 12 months EBITDA (Including the current period) numbers in a…
Hi I'm looking to speak to people who have used/are using the Docusign integration in their business. We are exploring the possibility, but we find the current setup quite limiting in terms of how an end user would interact with the integration, but also issues with concurrency and number field formats. Would appreciate…
A quick reminder of the Bulk Copy functionality. Bulk Copy allows you copy large volumes of data from one slice of a model to another in a single, optimised operation, instead of using formulas or imports. Use case: copy a version (RF1) into a prior year version (PY RF1) using a versions list to allow for year-on-year…
