If condition

[danny4202001]

Hello Friends,

Need to write this if statement in script but it errored out saying nested aggregation is not allowed 

all I need is the previous month's Year month YYYYMM in this case in 202212, Please let me know if I can write this anyother way

if(AccYearMonth>(year(AddYears(max(now()),-1) & (num(MONTH (AddMonths (MAX (now()), - 1))))))

using above statement hear

sum(if(AccYearMonth>(year(AddYears(max(now()),-1) & (num(MONTH (AddMonths (MAX (now()), - 1)))))) and AccYearMonth<$(vNextMonthYYYYMM), Quantity*PRICE)) as abcd

Thanks,

D

[Digvijay_Singh · Accepted solution]

I would suggest you do date compare rather than text compare, you can get previous month from today's date like this - 

Date(MonthEnd( Today(1) ,-1),'YYYYMM')

So compare your data model date field as -

if (Monthend(Datefield)=Date(MonthEnd( Today(1) ,-1),'YYYYMM'),true,false)

[danny4202001]

Hello Digvijay thank you for your reply

The below query is giving perfect results as a set analysis but when I write this in script its gives wrong results data before May 

vNextmonth1= May

sum({$<AccYearPeriod={">=$(vNextmonth1)"} Quantity_OO *UNIT_SELLING_PRICE_OO)--- results are perfect

sum(if(AccYearMonth>=($(vNextmonth1)), Quantity_OO * UNIT_SELLING_PRICE_OO)),'0' as FMTHGT3Open----------------------results are not right data includes from may and before 

Please help 

Thanks,

D