hello i want to simplify this code

[hafaneh]

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

[marcus_sommer]

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.

[rwunderlich]

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

-Rob