topic Re: hello i want to simplify this code in App Development

hello i want to simplify this code

hafaneh — Fri, 15 Nov 2024 21:24:58 GMT

i have this code i want to know to can i simplify it and make it less complex for the speed of the sheets ?

(if(Var1=0,count({<Date = {">=$(=YearStart(Today()))"}>}Distinct DOCNO), if(Var1=1,count({<DateNum={">=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Distinct DOCNO), if(Var1=2,count({<Date={"$(=DATE(TODAY()-1))"}>}Distinct DOCNO), if(Var1=3,count(Distinct DOCNO) )))) / if(Var1=0,Count({<[Date] = {">=$(=Date(AddYears(YearStart(Today()),-1)))<$(=Date(AddYears(Today()-1,-1)))"}, Year=>}Distinct DOCNO), if(Var1=1,Count({<DateNum={">=$(=Num(MonthStart(addyears(Max(DateNum),-1))))<=$(=num(addyears(Max(DateNum),-1)))"}, Year=>} Distinct DOCNO), if(Var1=2, count({<Date={"$(=DATE(addyears((TODAY()),-1)))"}, Year=>}Distinct DOCNO), if(Var1=3, count({<Year={"$(=Year(Addyears(Max(DateNum),-1)))"}>}Distinct DOCNO) )))) ) -1

Re: hello i want to simplify this code

marcus_sommer — Tue, 12 Sep 2023 10:19:01 GMT

You may outsource the set analysis condition in a variable, like here in varX:

if(if(Var1=0, '{<Date = {">=$(=YearStart(Today()))"}>}', ...)

and then using:

count($(varX) Distinct DOCNO)

Such approach will improve the performance because the number of aggregation-calculations is reduced but in regard to the complexity it's no big change. Of course the expressions itself is simpler but the logic must be maintained in two places.

Alternative would be to create appropriate flags of YTD, MTD, ... within the calendar and using them within the set analysis.

Re: hello i want to simplify this code

rwunderlich — Wed, 13 Sep 2023 23:57:06 GMT

If Var1 a single value at any given time? Or does Var1 vary row by row?

-Rob