Rolling 12 Months

[tchovanec]

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.

[tchovanec]

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]

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]

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]

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

[tchovanec]

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]

Hi Thomas

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

Regards