topic Re: adding a constant to a sum() and accumulation over weeks/months in App Development

adding a constant to a sum() and accumulation over weeks/months

Florian01 — Tue, 11 Jun 2024 07:34:30 GMT

Hello everyone,

I have following issue:

I have a line graph with Sum(A) as formula and I also implemented a Mastercalender allowing me to have calender Weeks and Months as dimension).

I want to use the accumulation option of the graph to show Sum(a) over calender Week and respectively months.

Now I have to add one constant K only one time to this sum so bascally an offset.

If I use "=Sum(A) + K' the accumulation does not work correctly because K will be added for each Week/month.

How to adapt the formula so that the constant K will be added only one time so will not be accumulated?

Chatgpt proposed me to solutions:
Solution 1 (for months):

RangeSum(
Above(
Sum({<Month>} A),
0,
RowNo(TOTAL)
)
) + Only({1} K)

Solution 2:

Sum(<Month>} A) + Only({1} K)

none of them work. I get 0 over the whole timerange for solution 2 and an exponential curve going way too high for solution 1.

Would be very Happy if I could get some help on this one.

Thanks 🙂

Best Regards,

Florian

Re: adding a constant to a sum() and accumulation over weeks/months

TauseefKhan — Tue, 11 Jun 2024 07:44:10 GMT

Hi @Florian01,

SET K = 10; // Adjust the value here accordingly

Ensure the Master Calendar is correctly implemented with Weeks and Months as dimensions.
Use the accumulation and add the constant ( K ) accordingly.

Weeks:
RangeSum(Above(Total Sum(A), 0, RowNo(TOTAL))) + if(RowNo(TOTAL) = 1, $(K), 0)

Months:
RangeSum(Above(Total Sum(A), 0, RowNo(TOTAL))) + if(RowNo(TOTAL) = 1, $(K), 0)

***Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.***

Re: adding a constant to a sum() and accumulation over weeks/months

Florian01 — Tue, 11 Jun 2024 07:46:37 GMT

thanks for the fast Reply 🙂 unfortunaltey with this solution I get the exponentional behavior from the solution 1 from ChatGpt.

Re: adding a constant to a sum() and accumulation over weeks/months

Florian01 — Tue, 11 Jun 2024 07:57:39 GMT

ok I solved it with Sum(A) + if(RowNo(TOTAL) = 1, $(K), 0)

Thanks for the help!