Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
Ensure INVOICE_DATE is stored as a date. Use Date() to format it explicitly.
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.
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
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
@jalanhart Use below expression
Sum({< [INVOICE_DATE] ={">=$(=date(MAX(INVOICE_DATE) - 365))<=$(=Max(INVOICE_DATE))"} >}NET_AMOUNT)
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)
the correct is with double quotes and addmonths function: Sum({< DATE ={">=$(=date(AddMonths(MAX(DATE),-12)))<=$(=Max(DATA))"} >} NET_AMOUNT)