Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I was trying to calculate a cagr (compound annual growth rate) at script level.
Formula for cagr:
CAGR = (current revenue / previous revenue) ^ (1/n) -1; where n is the total number of years (in my case, it's quarters)
I am trying to do the calculation in a resident table that pulls from a previously loaded combined revenue table. Please see below for scripts, not sure what's wrong but it will not calculate and also i have a long list of fields from the combined revenue qvd, do i need to list them all under group by?
CAGR_Temp:
Load
Fiscal_Qtr,
Total_Sales,
sum(if(Fiscal_Qtr = max( Fiscal_Qtr), Total_Sales, 0) as Current_FQ_Sales,
sum(if(Fiscal_Qtr = max(Fiscal_Qtr - 12), Total_Sales, 0) as Previous_FQ_Sales,
power((Current_FQ_Sales / Previous_FQ_Sales), (1/12)) - 1 as CAGR
Resident CombinedRevenue;
Group by ...
Thanks in advance!
Hi
Can you pl provide some sample data
Thanks
Padma
Provide sample data file..
Hi
Is this you want?
Can you please check if CAGR is what you are expecting?
Regards
Padma
Hi, I need to build a cagr chart like the screenshot attached above...
Thanks!
hi , i have done some thing for you , hope it helps you please find attachment
TEMP_ORGINAL:
LOAD * ,right(FQ,2)&mid(FQ,2,1) as SORTROW INLINE [
FQ, Total Sales
Q414, 54475
Q314, 50864
Q213, 53639
Q113, 50095
Q313, 50573
Q114, 54245
Q214, 51526
Q413, 51918
];
//FOR COUNT
COUNT:
Load distinct Count(FQ) as FQ_COUNT
resident TEMP_ORGINAL;
let vSCRIPTFQ_COUNT=Peek('FQ_COUNT',0,'COUNT');
left Join(TEMP_ORGINAL)
Load FQ,
'$(vSCRIPTFQ_COUNT)' as Count
Resident TEMP_ORGINAL;
FINAL:
Load *,
pow(([Total Sales]/PreviousRevenue),(1/[Count]))-1 as CAGR;
Load *,
rangesum(Previous([Total Sales]),0) as PreviousRevenue;
LOAd *
resident TEMP_ORGINAL Order by SORTROW asc ;
drop table TEMP_ORGINAL;
Hi! This is great! I was wondering though, I had the total sales calculated in the combined revenue qvd load,
alt(Ship_Value,0) + alt(POS_Revenue_Value,0) | as Total_Sales, |
instead of doing the inline table load, maybe I can just link it to the total sales in the previously loaded file? I am using a personal edition QV so am not able to view your qvw. I will try to do the count and the final calculation by joining it to the combined revenue qvd.
Thank you so much! I will let you know if that works!
Hi, I tried the scripts with some minor changes per what I described earlier. Scripts debug failed, with no error message. Also, I changed the count to 12, because I am trying to calculate the cagr for 3 years window. so for example if current FQ is Q2,14.. then the previous quarter should be Q2,11 (12 quarters back).
Would you please look at the scripts and advise where and what went wrong?
TIA!
CombinedRevenue:
LOAD %Calendar_Id,
%Transaction_Id,
%Material_Id,
%Product_Line_Id,
%End_Mkt_Id,
%Distributor_Id,
%Branch_Id,
%PV_Cust_Ship_Id,
%PV_Cust_Sold_Id,
%PV_Cust_Parent_Id,
%PV_Territory_Id,
%EC_Cust_Ship_Id,
%EC_Cust_Sold_Id,
%EC_Cust_Parent_Id,
%EC_Territory_Id,
Channel_Type,
Ship_Qty,
Ship_Value,
Ship_Direct_Cost,
Ship_Full_Cost,
Ship_Direct_Margin,
Ship_Full_Margin,
RevType,
POS_Qty,
POS_Revenue_Value,
POS_Resale_Value,
POS_Disti_Value,
POS_Direct_Cost,
POS_Full_Cost,
POS_Direct_Margin,
POS_Full_Margin,
POS_Disti_Margin,
applymap('Calendar_Map',%Calendar_Id,0) as Fiscal_Qtr,
applymap('Vintage_Year_Qtr_Map',%Material_Id,0) as Vintage_Year_Qtr,
alt(Ship_Value,0) + alt(POS_Revenue_Value,0) as Total_Sales,
alt(Ship_Qty,0) + alt(POS_Qty,0) as Total_Qty,
alt(Ship_Full_Cost,0) + alt(POS_Full_Cost,0) as Total_Full_Cost,
alt(Ship_Full_Margin,0) + alt(POS_Full_Margin,0) as Total_Full_Margin
FROM
CombinedRevenue.Qvd
(qvd);
Count:
Load Distinct
Count(Fiscal_Qtr) as FQ_Count
Resident CombinedRevenue;
let vSCRIPTFQ_COUNT=Peek('FQ_COUNT',0,'COUNT');
left Join(CombinedRevenue)
Load
Fiscal_Qtr,
'$(vSCRIPTFQ_COUNT)' as Count
Resident CombinedRevenue;
CAGR_Calc:
Load *,
pow(([Total_Sales]/PreviousRevenue),(1/[Count]))-1 as CAGR;
Load *,
rangesum(peek('FQ_Count',-12,CombinedRevenue), [Total_Sales],0) as PreviousRevenue;
Load *
resident CombinedRevenue;
hi what error you getting exactly , screenshot it and post here
you have so many dimensions so peek expression in script has to be changed. and one more thing sorting by Year is missing in your script, see my script for reference.
i would like to suggest that make another table with 2 fields FQ,Totalsales taking resident and except FQ field, rename all other fields to avoid synthetic key.
and do the procedure as i mentioned before .
FQ_CGGR_TABLE:
Load *,
left([Fiscal_Qtr],2)&mid([Fiscal_Qtr],2,1) as SORTROW; //am assuming that your fiscal_qtr field value are like Q112,etc etc.
Load Fiscal_Qtr,
sum([Total Sales]) as Revenue_cggr,
resident CombinedRevenue group by Fiscal_Qtr;
Count:
Load Distinct
Count(Fiscal_Qtr) as FQ_Count
Resident FQ_CGGR_TABLE;
let vSCRIPTFQ_COUNT=Peek('FQ_COUNT',0,'COUNT');
left Join(FQ_CGGR_TABLE)
Load
distinct
Fiscal_Qtr,
'$(vSCRIPTFQ_COUNT)' as Count
Resident FQ_CGGR_TABLE;
drop table Count;
FQ_CGGR_TABLE_FINAL:
Load *,
pow(([Revenue_cggr]/PreviousRevenue),(1/[Count]))-1 as CAGR;
Load *,
rangesum(previous(Revenue_cggr),0) as PreviousRevenue;
Load *,
rowno() as DUMROW
resident FQ_CGGR_TABLE order by SORTROW asc;
drop table FQ_CGGR_TABLE;
if you observe table viewer you will get two tables linked with Fiscal_Qtr as a linik.
Note : in above calculation of CGGR the resultant value is only wrt. to Fiscal Qtr, it wil not change if you select any other dimension except Fiscal_Qtr...
if you want to calculate wrt all dimensions in combined revenue the you need to change the peek exp in your script wrt to that.you need to post sample data.
Hi, that's the error message that i got from QV and when i clicked ok, it gives me the execution of script failed error.
Also, why do I need to sort the year? The Fiscal_Qtr values are labeled numerically ie., 20143 (3Q14) etc...