calculation for time balance property, formula, two pass

最新推荐文章于 2024-09-18 13:19:02 发布

转载最新推荐文章于 2024-09-18 13:19:02 发布 · 147 阅读

本文探讨了Churn率计算公式及在不同时间段的应用变化。针对Margin%和Churn指标的时间平衡属性和两阶段计算进行了详细解析，并通过案例说明如何确保计算结果准确。

say "Margin %" has a formula: Margin % Sales, and both Qtr1 and "Margin %" are dynamic.

if only time balance property is set, say First, then Qtr1->"Margin %" is equal to jan->"Margin %",
formula is overritten by time balance property.

now if "two pass" is set, then Qtr1->"Margin %" will be re-calculated based on formula.

about "Churn" Story:

basical formula: Churn=Net_Deacts/Avg_Subs*-1

IF (@ISGEN("Period",4) OR (@ISMBR("JAN YTD") OR @ISMBR("JAN QTD"))) Net_Deacts/Avg_Subs * -1;ELSEIF (@ISGEN("Period",3) AND NOT (@ISCHILD(YTD) OR @ISCHILD(QTD))) (Net_Deacts/Avg_Subs * -1)/3;ELSEIF (@ISMBR("Q1")) (Net_Deacts/Avg_Subs * -1)/3;ELSEIF (@ISMBR("Q2")) (Net_Deacts/Avg_Subs * -1)/3;ELSEIF (@ISMBR("Q3")) (Net_Deacts/Avg_Subs * -1)/3;ELSEIF (@ISMBR("Q4")) (Net_Deacts/Avg_Subs * -1)/3;ELSEIF (@ISMBR("FEB YTD")) (Net_Deacts/Avg_Subs * -1)/2;ELSEIF (@ISMBR("MAR YTD")) (Net_Deacts/Avg_Subs * -1)/3;ELSEIF (@ISMBR("APR YTD")) (Net_Deacts/Avg_Subs * -1)/4;ELSEIF (@ISMBR("MAY YTD")) (Net_Deacts/Avg_Subs * -1)/5;ELSEIF (@ISMBR("JUN YTD")) (Net_Deacts/Avg_Subs * -1)/6;ELSEIF (@ISMBR("JUL YTD")) (Net_Deacts/Avg_Subs * -1)/7;ELSEIF (@ISMBR("AUG YTD")) (Net_Deacts/Avg_Subs * -1)/8;ELSEIF (@ISMBR("SEP YTD")) (Net_Deacts/Avg_Subs * -1)/9;ELSEIF (@ISMBR("OCT YTD")) (Net_Deacts/Avg_Subs * -1)/10;ELSEIF (@ISMBR("NOV YTD")) (Net_Deacts/Avg_Subs * -1)/11;ELSEIF (@ISMBR("DEC YTD")) (Net_Deacts/Avg_Subs * -1)/12;ELSEIF (@ISMBR("FEB QTD")) (Net_Deacts/Avg_Subs * -1)/2;ELSEIF (@ISMBR("MAR QTD")) (Net_Deacts/Avg_Subs * -1)/3;ELSEIF (@ISMBR("APR QTD")) (Net_Deacts/Avg_Subs * -1)/1;ELSEIF (@ISMBR("MAY QTD")) (Net_Deacts/Avg_Subs * -1)/2;ELSEIF (@ISMBR("JUN QTD")) (Net_Deacts/Avg_Subs * -1)/3;ELSEIF (@ISMBR("JUL QTD")) (Net_Deacts/Avg_Subs * -1)/1;ELSEIF (@ISMBR("AUG QTD")) (Net_Deacts/Avg_Subs * -1)/2;ELSEIF (@ISMBR("SEP QTD")) (Net_Deacts/Avg_Subs * -1)/3;ELSEIF (@ISMBR("OCT QTD")) (Net_Deacts/Avg_Subs * -1)/1;ELSEIF (@ISMBR("NOV QTD")) (Net_Deacts/Avg_Subs * -1)/2;ELSEIF (@ISMBR("DEC QTD")) (Net_Deacts/Avg_Subs * -1)/3;ELSEIF (@ISMBR("1st Half Year")) (Net_Deacts/Avg_Subs * -1)/6;ELSEIF (@ISMBR("2nd Half Year")) (Net_Deacts/Avg_Subs * -1)/6;ELSEIF (@ISMBR("YearTotal")) (Net_Deacts/Avg_Subs * -1)/12;ELSE Net_Deacts/Avg_Subs * -1;ENDIF;

sinc e "None" can not be deployed, so i used Flow, which lead time balance property

to be none. this turn out "churn"->q1 is equal to sum of Jan, Feb, Mar. Why? since account member is calculated firstly and time secondly.

after change to "average" ( which is not helpful actually) and set to "two pass", get the

expected value: "Churn"->q1 formula is this: (Net_Deacts/Avg_Subs * -1)/3.

Net_Deacts->q1 is sum of 3 monthes: Net_Deacts->Jan+Net_Deacts->Feb+Net_Deacts->Mar

Avg_Subs->q1 is (Avg_Subs->Jan + Avg_Subs->Feb+Avg_Subs->Mar)/3

For Avg_Subs, it has "TB Average" and formula: (eop_subs+bop_subs) / 2:

for Jan, Feb, Mar,etc, it's equal to (eop_subs+bop_subs)/2 of each month; for Q1,

it's equal to (Avg_Subs->Jan + Avg_Subs->Feb+Avg_Subs->Mar)/3 since "TB Average" and no two pass

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8583032/viewspace-720766/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8583032/viewspace-720766/