MONTHLY TREND IN A LINE CHART

[#1]

I am  trying to show a monthly trend on a Line Chart. For Example If I select July I Should See a Line from Jan to July not just the Dot for July. How can I accomplish that

[jagan]

Hi,

Try this expression

=Sum({<YearDimensionName=, QuarterDimensionName=, MonthDimensionName=,

DateDimensionName={'>=$(=YearStart(Max(DateDimensionName)))<=$(=Date(Max(DateDimensionName)))'}>} Sales)

Replace YearDimensionName, QuarterDimensionName, MonthDimensionName, DateDimensionName with your actual dimension names.

Hope this helps you.

Regards,

Jagan.

[#3]

My Expresssion is very complicated

(

((If(Year=2014,'$66,200,000',If(Year=2013,'$66,200,000',If(Year=2012,'$66,000,000',If(Year=2011,'$64,500,000',If(Year=2010,'$66,800,000',
If(Year=2009,'$69,661,247')))))))
/
(num(Sum({<[Line of Business]={'HCS','PSL'}
,[Claim Type Code]=,Year={$(=Max(Year))}>}EXPENSE_PAID_YTD)-Sum({<[Line of Business]={'HCS','PSL'},
[Claim Type Code]=,Year={$(=Max(Year))}>}EXPENSE_RECOVERY_YTD))/Max(Month)*12)
+

(If(Year=2014,'$16,740',If(Year=2013,'$16,740',If(Year=2012,'$17,142',If(Year=2011,'$16,791',If(Year=2010,'$17,373',
If(Year=2009,'$17,193')))))))
/
(Num(AVG({<CloseAdmin_Year={$(=Max(Year))},[Claim Type Code]=E({<[Claim Type Code]={'SUSPENSE'}>}),[Claim Status Code]={'CLOSED'}
,[Line of Business]={'PSL','HCS'},NET_INDEMNITY_PAID={'0'},NET_EXPENSE_PAID=E({<NET_EXPENSE_PAID={'0'}>}),Trial_Status_Flag={'Not Trial','Empty'}>} NET_EXPENSE_PAID)))
+

(if(Year=2014,'$66,988',If(Year=2013,'$66,988',If(Year=2012,'$72,957',If(Year=2011,'$76,894',If(Year=2010,'$74,863',
If(Year=2009,'$67,278')))))))
/
Num(AVG({<[Claim Status Code]={'CLOSED'},CloseAdmin_Year={$(=Max(Year))},[Claim Type Code]=E({<[Claim Type Code]={'SUSPENSE'}>})
,[Line of Business]={'PSL','HCS'},Trial_Status_Flag={'Not Trial','Empty'},NET_INDEMNITY_PAID={">0"}>} NET_EXPENSE_PAID))
+

(If(Year=2014,'$90,660,465',If(Year=2013,'$90,660,465',If(Year=2012,'$100,900,000',If(Year=2011,'$100,600,000',
If(Year=2010,'$105,500,000',If(Year=2009,'$102,997,881')))))))
/
(Num(Sum({<Year={$(=Max(Year))}>}INDEMNITY_MILLION_FLAG)/Max(Month)*12))
+

(If(Year=2014,'$355,116',If(Year=2013,'$355,116',If(Year=2012,'$291,319',If(Year=2011,'$307,066',
If(Year=2010,'$309,275',If(Year=2009,'$317,534')))))))
/
NUM((Sum({<CloseAdmin_Year={$(=Max(Year))},NET_INDEMNITY_PAID={"<1000000>0"},Occurence_Number_Flag={'0'},Year={$(=Max(Year))}>} NET_INDEMNITY_PAID)+
Sum({<[Occurence No]={'=sum(NET_INDEMNITY_PAID)>0 and sum(NET_INDEMNITY_PAID)<1000000 and Year=Max(CloseAdmin_Year)'}>} NET_INDEMNITY_PAID))
/
(Count({<CloseAdmin_Year={$(=Max(Year))},NET_INDEMNITY_PAID={"<1000000>0"},Occurence_Number_Flag={'0'},Year={$(=Max(Year))}>} distinct [Claim No])+
Count({<[Occurence No]={'=sum(NET_INDEMNITY_PAID)>0 and sum(NET_INDEMNITY_PAID)<1000000 and Year=Max(CloseAdmin_Year)'}>} DISTINCT [Occurence No])))
+

(If(Year=2014,'0.240',If(Year=2013,'0.240',If(Year=2012,'$0.185',If(Year=2011,'0.185',If(Year=2010,'0.185',If(Year=2009,'0.185')))))))
/
(num((Count({<NET_INDEMNITY_PAID={">0"},NET_EXPENSE_PAID={">0"},[Claim Status Code]={'CLOSED'},[Line of Business]={'HCS','PSL'}
,[Claim Type Code]=E({<[Claim Type Code]={'SUSPENSE'}>}),CloseAdmin_Year={$(=Max(Year))}>} DISTINCT [Claim No])+
Count({<NET_INDEMNITY_PAID={">0"},NET_EXPENSE_PAID={'0'},[Claim Status Code]={'CLOSED'},[Line of Business]={'HCS','PSL'},
[Claim Type Code]=E({<[Claim Type Code]={'SUSPENSE'}>}),CloseAdmin_Year={$(=Max(Year))}>} DISTINCT [Claim No]))
/
(Count({<[Claim Status Code]={'CLOSED'},[Line of Business]={'HCS','PSL'},[Claim Type Code]=E({<[Claim Type Code]={'HANDHOLD','MEDLIC'}>})
,CloseAdmin_Year={$(=Max(Year))}>} DISTINCT [Claim No]))))
+
(num(Count({<[Claim Status Code]={'CLOSED'},[Line of Business]={'HCS','PSL'}
,CloseAdmin_Year={$(=Max(Year))}>} DISTINCT [Claim No])/2873/Max(Month)*12))
/
(If(Year=2014,'0.650',If(Year=2013,'0.650',If(Year=2012,'0.640',If(Year=2011,'0.625',If(Year=2010,'0.625',If(Year=2009,'0.590')))))))
+

(Num(Count({<[Claim Status Code]={'OPEN'},[Claim Type Code]={'SUSPENSE'},[Line of Business]={'HCS','PSL'},Open_Year={$(=Max(Year))}>} DISTINCT [Claim No])/
(Count({<[Claim Status Code]={'OPEN'},[Claim Type Code]={'SUSPENSE'},[Line of Business]={'HCS','PSL'},Open_Year={$(=Max(Year))}>} DISTINCT [Claim No])+
Count({<[Line of Business]={'HCS','PSL'},[Claim Type Code]=E({<[Claim Type Code]={'SUSPENSE'}>}),[Claim Status Code]=E({<[Claim Status Code]={'OPENERROR'}>}),
XOpen_Year={$(=Max(Year))}>} DISTINCT [Claim No]))))
/
(If(Year=2014,'0.467',If(Year=2013,'0.467',If(Year=2012,'0.430',If(Year=2011,'0.440',If(Year=2010,'0.430',If(Year=2009,'0.430')))))))
+
27.3/
Num(Avg({<[Claim Status Code]={'CLOSED'},[Line of Business]={'HCS','PSL'},[Claim Type Code]={'NOI','SUIT','CLAIM'},CloseAdmin_Year={$(=Max(Year))}>}
Claim_Days)*12/365)))/
9

How can I Trend this over Months

[jagan]

Hi,

You have data year wise, then how display this by month wise, so you need to split this by month.   Also it is not good practice to hard all values in the expressions with If(), try placing those values in Excel file and make it easier.

Regards,

Jagan.

[n1ef5ng1]

your expression is... amazingly long, maintainence will be diffucult. consider using variable.

happened to me, i have alot of reports that has extrapolation. means predicting the final volume after current month end. I was told the formula is today()-4. But to play safe, i use it on variable instead of coding it. (have over 50+ table on extrapolation). however i just found out that the formula is actually today()-3. I just have to go back to variable and change the formula instead of sourcing out those tables that containsn extrapolation

[#6]

hi,Ray

please find attachment

it will help you to take last n months

[#7]

No Attachments. Can you please attach one?

[#8]

please find attachment in previous comment