Skip to main content
Announcements
New: No-code data prep in Qlik Cloud Analytics™ TAKE A TOUR
cancel
Showing results for 
Search instead for 
Did you mean: 
jalanhart
Creator
Creator

Last 12 Months expression not working

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. 

jalanhart_0-1736296381425.png

 

Labels (2)
8 Replies
pallavi_96
Partner - Contributor III
Partner - Contributor III

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)

BrunPierre
Partner - Master
Partner - Master

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

jalanhart
Creator
Creator
Author

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.  

Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

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

 

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
jalanhart
Creator
Creator
Author

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

Bhushan_Mahajan
Contributor III
Contributor III

@jalanhart Use below expression

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

Vegar
MVP
MVP

@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)

eddyvargas
Contributor III
Contributor III

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