topic Re: Resident and group by in QlikView

Resident and group by

Anonymous — Sun, 20 Jul 2014 04:39:49 GMT

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!

Re: Resident and group by

qlikoqlik — Sun, 20 Jul 2014 06:36:07 GMT

Can you pl provide some sample data

Thanks

Padma

Re: Resident and group by

MK_QSL — Sun, 20 Jul 2014 08:13:19 GMT

Provide sample data file..

Re: Resident and group by

qlikoqlik — Sun, 20 Jul 2014 11:11:19 GMT

Is this you want?

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

Regards

Padma

Re: Resident and group by

Anonymous — Sun, 20 Jul 2014 13:56:38 GMT

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

Thanks!

Re: Resident and group by

preminqlik — Sun, 20 Jul 2014 15:09:21 GMT

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;

Re: Resident and group by

Anonymous — Sun, 20 Jul 2014 15:32:17 GMT

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!

Re: Resident and group by

Anonymous — Sun, 20 Jul 2014 19:49:14 GMT

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;

Re: Resident and group by

preminqlik — Mon, 21 Jul 2014 04:12:32 GMT

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.

Re: Resident and group by

Anonymous — Mon, 21 Jul 2014 13:13:53 GMT

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

Re: Resident and group by

preminqlik — Mon, 21 Jul 2014 17:32:22 GMT

hi changMY1,

Check your C drive memory space.. i think to get optimum performance maintain atleast 10gb free in C drive.

Re: Resident and group by

preminqlik — Mon, 21 Jul 2014 17:38:13 GMT

see here, whenever you deal with peek or previous functions, it depends on previously loaded value ..so if the previously loaded value to become correct we need to sort the value according to previous quarter in your example...so that in your example previous quarters value is recording in row of current quarter so on with the name of Previous revenue...observe in table box rows for clarity in each and every load ...you'll come to know that what exactly peek and previous do.

Re: Resident and group by

Anonymous — Mon, 21 Jul 2014 18:07:37 GMT

Thanks! I have 103GB left in my c drive, so not sure what's causing that problem. About the sort row - even though my Fiscal_Qtr is listed numeric, I'd still need to sort it? Is the counter necessary?

Thanks again for your help!!

Re: Resident and group by

Anonymous — Mon, 21 Jul 2014 18:21:20 GMT

Hi! Sorry - one more question.

rangesum(previous(Revenue_cggr),0) as PreviousRevenue;

Previous is used here, that would just refer to the previous qtr, correct? The previous revenue for me needs to be the point where it was 12 qtrs back, not the cumulative sale. For example, current qtr sales at 20143(Q314), and previous qtr sales needs to be 20113 or Q311. Can I just use sum of sales for Fiscal_Qtr minus 12?

Please advise, thanks again for your help.

Re: Resident and group by

preminqlik — Tue, 22 Jul 2014 02:12:21 GMT

do you want it in script level or front end ?

Re: Resident and group by

Anonymous — Tue, 22 Jul 2014 02:16:13 GMT

Hi ! it doesn't really matter to me, I was only able to get one period cagr at object level, not the line chart i was trying to achieve (per the screenshot from few days back).. that's why i decided to do it at script level.

Thanks again!

Re: Resident and group by

Anonymous — Tue, 22 Jul 2014 02:55:58 GMT

Hi, I forgot to mention that in the master calendar, I have the FQ_Counter and curr qtr flag, can i do a set analysis based on these? or do i need to create more variables or flags?

Thanks in advance!

Re: Resident and group by

Anonymous — Tue, 22 Jul 2014 22:56:18 GMT

Hi… help, pleaseee?

Thanks,