Resident and group by

[Anonymous]

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!

[qlikoqlik]

Hi

Can you pl provide some sample data

Thanks

Padma

[MK_QSL]

Provide sample data file..

[qlikoqlik]

Hi

Is this you want?

Can you please check if CAGR is what you are expecting?

Regards

Padma

[Anonymous]

Hi, I need to build a cagr chart like the screenshot attached above...

Thanks!

[preminqlik]

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;

[Anonymous]

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!

[Anonymous]

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;

[preminqlik]

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.

[Anonymous]

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...