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

[#2]

HI Tresesco,

I did try using the expression  you have suggested in my original application But I find that it is not returning any value when the comparison operators like < and > are used within the set expressions

The expression that i have been using in my original application is this-

sum({<CommonYear=, CommonMonth=,[Common Date]={">$(=Date(Addmonths(Max([Common Date]),-12),'DD/MM/YYYY'))<=$(=Date(Max([Common Date]),'DD/MM/YYYY'))"}>}MetricValue)

the above expression works fine when the filter for common date is split and '=' is used instead of '<' and' >' as

sum

({<CommonYear=, CommonMonth=,[Common Date]={"=$(=Date(Max([Common Date]),'DD/MM/YYYY'))"}>}MetricValue)

or

sum({<CommonYear=, CommonMonth=,[Common Date]={">$(=Date(Addmonths(Max([Common Date]),-12),'DD/MM/YYYY'))"}>}MetricValue)

So the Problem is not the date format.Can you please let me know where I am going wrong in the first expression as I am not getting any data

[tresesco]

Still I guess, it would be format issue. Comparison is not working properly. Please post your app with sample data.

[#4]

Hi Tresesco,

I tried changing the format and the expression is working fine as you suggested.But I see that in my application when i select Year 2013 and Month Mar data is displayed only for Mar 2013 and the data for the previous 12 months are not getting displayed.So can you Please look into this issue and let me know where I have gone wrong in getting the desired output.

I could not understand why you have multiplied year with 100 in the below 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))

So can you Please explain the same as well

I have attached the sample document for reference

[tresesco]

Exclude IF from the expression and try:

=sum({<Flag1={'-1'},Category={'Program Efficiency'},CommonYear=,CommonMonth=,[Common Date]={">$(=Date(Addmonths(Max([Common Date]),-12),'M/D/YYYY')) <=$(=Date(Max([Common Date]),'M/D/YYYY'))"}>}Metric_Value)

[#6]

Thank you so much Tresco.Its working as intended .Can you please explain the logic that you have used for sorting.I could not understand why you are multiplying year with 100?

Incase i want to see the data in ascending order what should be the logic?