Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Last 12 months Expression

Hi All,

I have a date field as 2015-04-01 for every month. And also Year and Month field created from the Date field.

Now there is a need to display the sum(sales) for last 12 months. I am trying to create the expression to limit the months from last year present month to this year month(Today()) , but its not working.

It will be great if I can get some help.

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Hi Rahul,

i tried it within one of my apps

this one works ( i have older data and other dateformat)

=sum({<Date={">=$(=date(addmonths(monthstart(today()),-60),'MM/DD/YYYY'))<=date(monthstart(today()),'MM/DD/YYYY')"}>}Sales)

so if you may try this one it might work

=sum({<Datefield={">=$(=date(addmonths(monthstart(today()),-12),'YYYY-MM-DD'))<=date(monthstart(today()),'YYYY'-MM-DD)"}>}sales)

View solution in original post

10 Replies
Anonymous
Not applicable
Author

try

sum({<yourdatefield={'>=addmonths(monthstart(today()),-12)<=monthstart(today()'}>}sales)

Not applicable
Author

Its saying Nothing to Display. I have tried without any Dimension also and With month as the dimension.

Anonymous
Not applicable
Author

how the data Looks like if you define a TextBox with Expression =today()?

also try my Expression in a TextBox, does it Show No Data? Are there any

selections? can you clear them?

Not applicable
Author

The today() expr is showing the date as 7/6/2015 which is different from my format. However your expression is showing 0.00 without any selection.

Anonymous
Not applicable
Author

the dateformat must be identical.

if today is showing D/M/YYYY and your datefield Shows YYYY-MM-DD you Need to convert the Format.

try this

sum({<yourdatefield={'>=date(addmonths(monthstart(today()),-12),'YYYY-MM-DD')<=date(monthstart(today(),'YYYY-MM-DD')'}>}sales)

Not applicable
Author

The expr is giving error.

Not applicable
Author

Hi Rudolf,

I have created an extra date field with the same format. But still its not showing any data in the chart. The same msg of nothing to Display is coming.

Anonymous
Not applicable
Author

Hi Rahul,

i tried it within one of my apps

this one works ( i have older data and other dateformat)

=sum({<Date={">=$(=date(addmonths(monthstart(today()),-60),'MM/DD/YYYY'))<=date(monthstart(today()),'MM/DD/YYYY')"}>}Sales)

so if you may try this one it might work

=sum({<Datefield={">=$(=date(addmonths(monthstart(today()),-12),'YYYY-MM-DD'))<=date(monthstart(today()),'YYYY'-MM-DD)"}>}sales)

Not applicable
Author

Hi Rudolf,

Thanks alot.

Great. It worked . Now the only change I will have to do is to make this based on selected month. Means to go 12 months back from the selected month.