Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
tchovanec
Creator
Creator

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
tchovanec
Creator
Creator
Author

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?

aveeeeeee7en
Specialist III
Specialist III

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

wallinpeter
Contributor III
Contributor III

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.

tchovanec
Creator
Creator
Author

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

tchovanec
Creator
Creator
Author

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.

aveeeeeee7en
Specialist III
Specialist III

Hi Thomas

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

Regards