Question Re: model Variable and calculated account formula in Technology Q&A

model Variable and calculated account formula

daniel_weisblut86 — Wed, 06 Dec 2023 21:38:31 GMT

Need your help pls with model Varibale and calculated account formula

I have number of passenger's for 2023 and number of planes is calculate (no. passengers / seat on plane)

this is the simple part

for 2024 - 2032 i want in each year the number of planes to be 3% above last year

I created a Variables parameter Base year

Now in the Number of Planes Account i want to write a formula to calculate
If Year = Base Year then calculate the Number of Planes

and If Year is Larger then Base Year Take the number of planes in the base year and calculate for each year increase by 3%

Appreciate You help How is the best way to define that

Thank you

Re: model Variable and calculated account formula

N1kh1l — Thu, 07 Dec 2023 07:48:11 GMT

daniel_weisblut

daniel_weisblutFirst define these 2 helper accounts accounts

If Year = Base Year then calculate the Number of Planes

and If Year is Larger then Base Year Take the number of planes in the base year and calculate for each year increase by 3%

[calc_plane]=[no. passengers ]/[ seat on plane]

[calc_plane_baseyr]=RESTRICT([calc_plane] ,[d/Time]=['BaseYear'] )

[calc_plane_future]=ITERATE(PRIOR () * 1.03 ,[calc_plane_baseyr],[d/Time])

IF(TONUMBER([d/Time].[p/YEAR] ) >TONUMBER(['BaseYear'] ),[calc_plane_future] ,IF(TONUMBER([d/Time].[p/YEAR] ) =TONUMBER(['BaseYear'] ),[calc_plane],0))

Note: ['BaseYear'] this is my model vaiable ID

Example.

My final formula

Output:

variable selection

In Year 2022 it shows the original calculation for no of planes, for future years it incrmenets by 3% over base yea value.

Nikhil

Re: model Variable and calculated account formula

daniel_weisblut86 — Thu, 07 Dec 2023 10:18:35 GMT

Hi Nikhil

Thank you for the quick respond

Regarding The last formula

where do i define it?
in the model? or at the story?

IF(TONUMBER([d/Time].[p/YEAR] ) >TONUMBER(['BaseYear'] ),[calc_plane_future] ,IF(TONUMBER([d/Time].[p/YEAR] ) =TONUMBER(['BaseYear'] ),[calc_plane],0))

Thank you

Re: model Variable and calculated account formula

N1kh1l — Thu, 07 Dec 2023 11:24:35 GMT

daniel_weisblut

In the model in calculated account.

Br.

Nikhil

Re: model Variable and calculated account formula

daniel_weisblut86 — Thu, 07 Dec 2023 13:03:42 GMT

Its in separate account?

You just wrote the formula and didnt mention in which account

Thank you very much

Re: model Variable and calculated account formula

N1kh1l — Thu, 07 Dec 2023 13:09:58 GMT

daniel_weisblut

This is your [Number of Planes] seperate account which will show calculated value (no. passengers / seat on plane) for base year and 3% increment over base year for future years.

Br.

Nikhil

Re: model Variable and calculated account formula

daniel_weisblut86 — Thu, 07 Dec 2023 21:42:05 GMT

I put everything like you write but something is not working

When i put 2023 base year

I input no.passengers in 2023 24K its working great
In 2024 i input no.passengers 26K its not calculate Numberofplane

Appreciated you help

Daniel

Re: model Variable and calculated account formula

N1kh1l — Fri, 08 Dec 2023 15:37:55 GMT

daniel_weisblut

daniel_weisblutThis does not look as simple but I have tried to work it out. I would still advise you to check if you can use Data Action for this.

Account members

Formulas:

[calc_plane]=[No_of_pax]/[Seat_on_plane]

[calc_plane_baseyr]=LOOKUP([calc_plane] ,[d/Date]=['BaseYear'] )

[calc_plane_future]=ITERATE(IF([d/Date].[p/YEAR]>['BaseYear'] ,PRIOR(),[calc_plane_baseyr]/1.03) * 1.03 ,[calc_plane_baseyr],[d/Date])

[No_of_plane]=IF([d/Date].[p/YEAR]  >['BaseYear'],[calc_plane_future] ,
IF([d/Date].[p/YEAR] =['BaseYear'],[calc_plane_baseyr],0))

Below I am using as you did not say what happens to year prior to Base Yr, so I am keeping them as calc_plane=[No_of_pax]/[Seat_on_plane]

[No_of_plane_1]=IF([No_of_plane]=0 ,[calc_plane] ,[No_of_plane] )

Output:

Base Yr= 2023, 159 in 2023 and then 3% increment over years

Base Yr= 2024,172 in 2024 and then 3% increment over years, 159 in 2023

Base Yr= 2025,185 in 2024 and then 3% increment over years, 159 in 2023 nd 172 in 2024

Hope this helps !!

Please accept/upvote if this helps you

Nikhil

Re: model Variable and calculated account formula

daniel_weisblut86 — Sat, 09 Dec 2023 21:43:27 GMT

Thank you very much

You know i tried everything you wrote and it didn't work

And then i try it in the classic model and its worked

I Built the my model in the new model with Account and 1 measure

Do you have an idea why the iterate and period functions are not working in the new model? (i try to build simple example and its not working)

Again Thank you a lot

Re: model Variable and calculated account formula

N1kh1l — Sun, 10 Dec 2023 06:55:07 GMT

daniel_weisblut

My example was fully on new model itself. Below is the simple model used.

Br.

Nikhil

Re: model Variable and calculated account formula

daniel_weisblut86 — Sun, 10 Dec 2023 07:11:59 GMT

Thank you very much

You help me a lot

Have a good week