topic Limit the Range Sum in App Development

Limit the Range Sum

ali_hijazi — Tue, 23 May 2023 07:54:19 GMT

Hello
I'm working on an expression that calculates the accumulative variance

the main expression gets values up to April 2023 (limited in set analysis)
then I used Range Sum to calculate the variance
then I chose Accumulation in the modifier to calculate the accumulative variance
the numbers are correct but I want to display values up to 2023 04

main Expression is :
Sum({<MonthDiff={0},Scenario={Actual},Date_Num={"<$(=Max({1<[Is Current Month]={1}>}[Date_Num]))"}>}Periodic)

the expression that calculates the variance is :
fabs
(rangesum(
Sum( {<MonthDiff={0},Scenario={Actual},Date_Num={"<$(=Max({1<[Is Current Month]={1}>}[Date_Num])-1)"}>}Periodic )
,
-1*above( Sum( {<[Year Month]=, MonthDiff={0},Scenario={Actual},Date_Num={"<$(=Max({1<[Is Current Month]={1}>}[Date_Num])-1)"}>}Periodic ))*avg(1)
)

kindly advise

Re: Limit the Range Sum

marcus_sommer — Tue, 23 May 2023 11:03:33 GMT

You may just use your main-expression as a boolean check, like:

VarianceExpression * sign(MainExpression)

Re: Limit the Range Sum

ali_hijazi — Tue, 23 May 2023 13:53:51 GMT

may you please elaborate more?

Re: Limit the Range Sum

marcus_sommer — Tue, 23 May 2023 14:32:27 GMT

It's not complicated. Your fabs(rangesum(above(...))) creates future-results for not existing values just repeating the last result for all further exists dimension-values. In general it should be possible to add further condition within this variance-expression but it won't be trivial. Much easier is to put a boolean check on the outside. This means for all existing data your main-expression will return a (positive) value and sign() returns as a wrapper 1 and the not existing data results in 0 and sign returned 0 which means in the end that your variance-expression is multiplied with 1 respectively 0 and the zero-results could be hidden per layout-option.

Beside using the MainExpression as check you might be also just add a 0/1 flag within the calendar related to dates <= monthend/montstart of today() respectively are >= and then using the flag as factor or as further dimension/expression-condition.