Dynamic Trend Display

[#1]

Hi,

I am working on a requirement wherein I need to show a chart that displays values of the past 12 months including the current selection.i.e., for eg if i have selected 2013 September the chart must display values for month starting from 2013 Sep to 2012 Aug in the same order and incase i select 2012 Jul the chart must display values for month starting from 2012 Jul to 2011 Jun in the respective order and suppose if no selection is made it must consider the current year and current month-1 (i.e., 2013 Nov) and display values for 2013 Nov to 2012 Oct. Can anyone Please let me know how this could be accomplished.I have attached a sample document for reference

[tresesco]

Try expression like:

=sum({<My_Year=, My_Month=,My_date={">$(=Date(Addmonths(Max(My_date),-12),'M/D/YYYY')) <=$(=Date(Max(My_date),'M/D/YYYY'))"}>} Value)

PFA

[suresh_rawat]

Hi Anushree,

This is working fine on my application, hope it will work out for you.

sum({1<Date = {">=$(=MonthStart(Max(Date), -11))<=$(=MonthEnd(Max(Date)))"}>}Data)

Regards

Suresh Rawat

[#4]

Anushree,

sum({1<My_date = {">=$(=MonthStart(Max(My_date), -11))<=$(=MonthEnd(Max(My_date)))"}>}Value)

i think my attachment is the correct solution you are looking for

Regards

Nuthan

[#5]

HI Tresesco,

The application that you have put up works as per the requirement but it is not sorted accordingly.Can you please let me know what could be done so that the data  is also sorted ie., I would want to see the data in the descending order .

For eg if I have selected Mar 2012 I would want to see the data starting from Mar 2012,Feb 2012,Jan 2012,Dec 2011... to Apr 2011

[#6]

Nuthan,

I see that the application put up by you is not reflecting the correct data.

For eg when I select 2013 Mar the value displayed for 2012 Apr in the chart is not correct.Can you please look into it

[tresesco]

Hi,

Sort expression:

=Only({<My_Year=, My_Month=,My_date={">$(=Date(Addmonths(Max(My_date),-12),'M/D/YYYY')) <=$(=Date(Max(My_date),'M/D/YYYY'))"}>} My_Year*100+Num(My_Month))  // Descending for the second dimension

PFA

Note: Things could have been easier had you have a field calculated in the script as: MonthName( Date) as MonthName.

[#8]

Can you Please let me know how I could use MonthName and ease up the application.

Thanks & Regards,

Anushree Shetty

[tresesco]

Create a new field like:

Load

          Date,

          MonthName(Date) as MonthName

from<>;

In the report take this MonthName as second dimension.

[#10]

Anushree,

Check out this attachment