topic Re: Last 12 Months expression not working in App Development

Last 12 Months expression not working

jalanhart — Wed, 08 Jan 2025 00:33:46 GMT

Hello,

I have an expression that I've been trying to show the sum of sales over the last 12 months and I can not seem to get the formula to report anything other than $0.

here is the expression Sum({< [INVOICE_DATE] ={'>=$(=date(MAX(INVOICE_DATE) - 365))<=$(=Max(INVOICE_DATE))'} >}NET_AMOUNT)

I know it works in the function expression checker at the bottom because it has the dates I want filled in, but the expression does not work.

Re: Last 12 Months expression not working

pallavi_96 — Wed, 08 Jan 2025 08:03:44 GMT

Hi @jalanhart

Try to use below expression

=Sum({$<Year=, Month=, [Year Month]={">=$(=Date(MonthStart(AddMonths(Min(Date),-11)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(Date),0)),'YYYYMM'))"}>} sales)

Re: Last 12 Months expression not working

BrunPierre — Wed, 08 Jan 2025 08:16:41 GMT

Ensure INVOICE_DATE is stored as a date. Use Date() to format it explicitly.

Re: Last 12 Months expression not working

jalanhart — Thu, 09 Jan 2025 00:27:31 GMT

thanks! this seems to be working correctly, though I would still like to have it be an actual 365 days but when i tried to replace the first part it didn't work. I also tried to add [date]= after the year month and that did not work either.

Re: Last 12 Months expression not working

Lech_Miszkiewicz — Thu, 09 Jan 2025 00:59:47 GMT

Hi @jalanhart

I would just state the obvious: instead of using autocalendar it is much better practice to use actual calendar which build by yourself with all fields created in the model and not derived from the field.

Once you do that you can ensure that the type of the date field is correct as well as you can introduce to your calendar flags you may require making your set analysis simpler.

Importantly you will also make your app performing better.

cheers

Re: Last 12 Months expression not working

jalanhart — Thu, 09 Jan 2025 01:02:18 GMT

Thanks for the advice! it's something I will implement eventually I'm sure but personally I really like the auto calendar . . . especially in charts where time is my independent variable.

but i also know that making flags and such are easier if I do them myself

Re: Last 12 Months expression not working

Bhushan_Mahajan — Thu, 09 Jan 2025 04:29:25 GMT

@jalanhart Use below expression

Sum({< [INVOICE_DATE] ={">=$(=date(MAX(INVOICE_DATE) - 365))<=$(=Max(INVOICE_DATE))"} >}NET_AMOUNT)

Re: Last 12 Months expression not working

Vegar — Thu, 09 Jan 2025 12:30:45 GMT

@jalanhart

I think the main issue with your original expression was the use of quotes. I suggest you wrap ' ' around the data strings, and " " around the whole value that is to be evaluated as below.

Sum({< [INVOICE_DATE] ={">='$(=date(MAX(INVOICE_DATE) - 365))'<='$(=Max(INVOICE_DATE))'"} >}NET_AMOUNT)

Re: Last 12 Months expression not working

eddyvargas — Thu, 09 Jan 2025 13:20:35 GMT

the correct is with double quotes and addmonths function: Sum({< DATE ={">=$(=date(AddMonths(MAX(DATE),-12)))<=$(=Max(DATA))"} >} NET_AMOUNT)