Formula to have trend line over Line chart in OBIEE:
((SUM(( Column name )) * 100)*CAST(RCOUNT(1) AS INTEGER)) - COUNT( Column name )) * 100)*Avg( Column name )) * 100)*Avg(RCOUNT(1)))/(SUM(POWER(RCOUNT(1), 2))-COUNT( Column name )) * 100)*POWER(Avg(RCOUNT(1)),2)))* RCOUNT(1) + ((Avg( Column name )) * 100) * SUM(POWER(RCOUNT(1), 2))-Avg(RCOUNT(1))* SUM(( Column name )) * 100)*CAST(RCOUNT(1) AS INTEGER)))/(SUM(POWER(RCOUNT(1), 2))-COUNT( Column name )) * 100)*POWER(Avg(RCOUNT(1)),2)))
Sample Line Chart with Trend Line :
Below Formula worked..
ReplyDelete((SUM((Column Name))*100)*CAST(RCOUNT(1) AS INTEGER))- (COUNT(Column Name) * 100)*Avg(Column Name) * 100*Avg(RCOUNT(1))/(SUM(POWER(RCOUNT(1), 2))) -COUNT((Column Name)* 100)*POWER(Avg(RCOUNT(1)),2) * RCOUNT(1)+ ((Avg(Column Name)) * 100)* SUM(POWER(RCOUNT(1), 2))-Avg(RCOUNT(1))* SUM((Column Name)) * 100*CAST(RCOUNT(1) AS INTEGER)/(SUM(POWER(RCOUNT(1), 2)))-COUNT(Column Name) * 100*POWER(Avg(RCOUNT(1)),2)
can you share your email ?
ReplyDeletehareeobiee@gmail.com
DeleteHariharasudhan,
ReplyDeleteThank's for sharing this formula.
I have a bar graph that the horizontal axis is my cost and the vertical axis is the months.
I need to make a trend line of my cost, So should i write "Cost" instead of "Column name"?
Thank you!
Susana
This works much better. Not sure where you got the multiples of 100:
ReplyDelete((SUM(CAST(RCOUNT(1) AS INTEGER) * MEASURE) - COUNT(RCOUNT(1)) * Avg(RCOUNT(1)) * AVG(MEASURE)) / (SUM(POWER(RCOUNT(1), 2)) - COUNT(RCOUNT(1)) * POWER(AVG(RCOUNT(1)), 2))) * RCOUNT(1) + (Avg(MEASURE) * SUM(POWER(RCOUNT(1), 2)) - Avg(RCOUNT(1)) * SUM(RCOUNT(1) * MEASURE)) / (SUM(POWER(RCOUNT(1), 2)) - COUNT(RCOUNT(1)) * POWER(AVG(RCOUNT(1)), 2))