Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tchovanec
Creator II
Creator II

Rolling 12 Months

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.

15 Replies
wallinpeter
Contributor III
Contributor III

Try Adding...

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

i.e.

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

whiteline
Master II
Master II

Hi.

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

tchovanec
Creator II
Creator II
Author

I tried this with no luck

tchovanec
Creator II
Creator II
Author

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

whiteline
Master II
Master II

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.

wallinpeter
Contributor III
Contributor III

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.

tchovanec
Creator II
Creator II
Author

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)

aveeeeeee7en
Specialist III
Specialist III

Hi Thomas

Try this:

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

Regards

Aviral Nag

tchovanec
Creator II
Creator II
Author

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)