topic Last 12 months Expression in QlikView

Last 12 months Expression

Sun, 07 Jun 2015 15:10:20 GMT

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.

Re: Last 12 months Expression

Anonymous — Sun, 07 Jun 2015 15:16:15 GMT

try

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

Re: Last 12 months Expression

Sun, 07 Jun 2015 15:25:07 GMT

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

Re: Last 12 months Expression

Anonymous — Sun, 07 Jun 2015 15:27:51 GMT

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?

Re: Last 12 months Expression

Sun, 07 Jun 2015 15:33:31 GMT

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.

Re: Last 12 months Expression

Anonymous — Sun, 07 Jun 2015 15:40:33 GMT

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)

Re: Last 12 months Expression

Sun, 07 Jun 2015 15:47:48 GMT

The expr is giving error.

Re: Last 12 months Expression

Sun, 07 Jun 2015 16:18:12 GMT

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.

Re: Last 12 months Expression

Anonymous — Sun, 07 Jun 2015 16:22:04 GMT

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)

Re: Last 12 months Expression

Sun, 07 Jun 2015 16:31:54 GMT

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.

Re: Last 12 months Expression

Anonymous — Sun, 07 Jun 2015 16:36:29 GMT

Hi Rahul,

Try this expression

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=MonthStart(Today(), -11))<=$(=Today())’}>} Sales )

Hope it will helpful!!