topic Re: Rolling 12 Months in QlikView

Rolling 12 Months

tchovanec — Fri, 28 Feb 2014 20:44:11 GMT

I have an app that I want to show data from the date selected minus 12 months. So if they choose Jan-2012, I want to show Jan-2011. I am trying to accomplish this without using an island calendar. I tried using the following set analysis, but it doesn't work because once I select a date from my calendar all the possible values of DT_PAT_ARR are not 12 months prior.

,DT_PAT_ARR = {">= $(=(ADDMONTHS(MONTHSTART(MAX(Date)),-12))) <= $(=(MAX(Date)))"}

My calendar is joined to my data using DT_PAT_ARR. Any ideas would be greatly appreciated. Thank you.

Re: Rolling 12 Months

wallinpeter — Fri, 28 Feb 2014 20:50:53 GMT

Try Adding...

Date={'*'} to your set statement.

i.e.

,DT_PAT_ARR = {">= $(=(ADDMONTHS(MONTHSTART(MAX(Date)),-12))) <= $(=(MAX(Date)))"},Date={'*'}

Re: Rolling 12 Months

whiteline — Fri, 28 Feb 2014 20:51:42 GMT

Hi.

I guess you also have to clear the Date field, otherwise the interaction of sets will be empty.

Re: Rolling 12 Months

tchovanec — Fri, 28 Feb 2014 20:53:31 GMT

I tried this with no luck

Re: Rolling 12 Months

tchovanec — Fri, 28 Feb 2014 20:54:04 GMT

How can I clear the date selection but still make sure I am going 12 months back from the date I selected?

Re: Rolling 12 Months

whiteline — Fri, 28 Feb 2014 20:56:30 GMT

I though you've already set the appropriate DT_PAT_ARR for that don't you ?

It's hard to locate the problem since we don't know the exact relation between the field that you use to set up and the Date field.

Re: Rolling 12 Months

wallinpeter — Fri, 28 Feb 2014 20:58:18 GMT

You need to have...

Date={'*'}

where "Date" equals the Date selection value.

You stated when they select "Jan-2012". What is the name of this field in the listbox. This needs to be what is in set statement.

Re: Rolling 12 Months

tchovanec — Fri, 28 Feb 2014 21:00:00 GMT

Here is my full expression. The list box where they are selecting the dates is the using the "Date" field.

AVG(

{$<

LINE = {1}

,EVENT_TYPE = {50,65}

,ED_DISPOSISTION_TYPE =

,DT_PAT_ARR = {">= $(=(ADDMONTHS(MONTHSTART(MAX(Date)),-12))) <= $(=(MAX(Date)))"

INTV_ED_LOS_MINS)

Re: Rolling 12 Months

aveeeeeee7en — Fri, 28 Feb 2014 21:01:09 GMT

Hi Thomas

Try this:

sum({1<DT_PAT_ARR={">=$(=addmonths(monthend(max(Date))+1,-12)) <=$(=max(Date))"}>}Sales)

Regards

Aviral Nag

Re: Rolling 12 Months

tchovanec — Fri, 28 Feb 2014 21:01:38 GMT

Here is my full expression. The list box where they are selecting the dates is the using the "Date" field. Where does Date={'*'} need to go. As of now I select Jan 2011 and it causes my DT_PAT_ARR to only have Jan-2011 as possible values because the Date calendar is joined on DT_PAT_ARR.

AVG(

{$<

LINE = {1}

,EVENT_TYPE = {50,65}

,ED_DISPOSISTION_TYPE =

,DT_PAT_ARR = {">= $(=(ADDMONTHS(MONTHSTART(MAX(Date)),-12))) <= $(=(MAX(Date)))"

INTV_ED_LOS_MINS)

Re: Rolling 12 Months

tchovanec — Fri, 28 Feb 2014 21:04:49 GMT

Nag,

Thank you. That did go back 12 months. Now I have another question. If I have Feb-2012 selected, I want to show Feb-2011 data. So, go back 12 months from my date selected. Any thoughts?

Re: Rolling 12 Months

aveeeeeee7en — Fri, 28 Feb 2014 21:16:38 GMT

Try this:

sum({1<DT_PAT_ARR={">=$(=addmonths(monthend(max(Date),)+1,-24)) <=$(=addmonths(max(Date),-12))"}>}Sales)

Is this what you want???

Regards

Aviral Nag

Re: Rolling 12 Months

wallinpeter — Fri, 28 Feb 2014 21:29:43 GMT

AVG(

{$<

LINE = {1}

,EVENT_TYPE = {50,65}

,ED_DISPOSISTION_TYPE =

,DT_PAT_ARR = {">= $(=(ADDMONTHS(MONTHSTART(MAX(Date)),-12))) <= $(=(MAX(Date)))"

,Date = {'*'}

INTV_ED_LOS_MINS)

I am not a fan of Nag's solution, because use 1, instead of $ will cause expression to ignore All selections, by using Date={*} you are basically saying...do not filter the data on the Date field.

Re: Rolling 12 Months

tchovanec — Mon, 03 Mar 2014 12:49:44 GMT

Peter, I have tried this but it doesn't seem to work. It keeps returning a NULL value

Re: Rolling 12 Months

tchovanec — Mon, 03 Mar 2014 12:55:41 GMT

Nag, Peter was right that using 1 , instead of $ will cause expression to ignore All selections. This isn't the behavior I was looking for.

Re: Rolling 12 Months

aveeeeeee7en — Mon, 03 Mar 2014 13:40:04 GMT

Hi Thomas

Try with $, it will return the sales for current selection.

Regards