topic Re: Growth over two Years in QlikView

Growth over two Years

nevilledhamsiri — Fri, 09 Mar 2018 15:35:06 GMT

Appreciate if the reference is made to propose how best the growth could be computed based on the expression applied for the calculation of MO, NM & Total sales. Expressions applied as follows!

SUM({<MO_NM_SALES={'MOTOR'},ME_CODE>}TOTAL)

SUM({<MO_NM_SALES={'NON_MOTOR'},ME_CODE>}TOTAL)

SUM({<MO_NM_SALES={'MOTOR'},ME_CODE>}TOTAL)+SUM({<MO_NM_SALES={'NON_MOTOR'},ME_CODE>}TOTAL)

Thanks

Neville

Re: Growth over two Years

nevilledhamsiri — Fri, 09 Mar 2018 16:04:35 GMT

Dear All!

Please see to this.

I urgently requires this calculation to be done!

Best Regards

Neville

Re: Growth over two Years

Digvijay_Singh — Fri, 09 Mar 2018 16:18:49 GMT

Do you have master calendar with date related fields associated to your fact table date field? You need to add date Year filter in all your expressions, may be like this to calculate the growth % from last year to this year-

(SUM({<MO_NM_SALES={'MOTOR'},ME_CODE,Year={"=$(=Year(DateField))"}>}TOTAL)

SUM({<MO_NM_SALES={'MOTOR'},ME_CODE,Year={"=$(=Year(DateField)-1)"}>}TOTAL))

SUM({<MO_NM_SALES={'MOTOR'},ME_CODE,Year={"=$(=Year(DateField)-1)"}>}TOTAL)

Do you want Yeartodate growth? For that you would need different set expression, something like Date >=Yearstart and <today() or use of yeartodate function.

Re: Growth over two Years

nevilledhamsiri — Fri, 09 Mar 2018 17:10:57 GMT

Hi Digvijay

When this expression given, an error appear.Can you please help me to correct this. Following calender I have loaded.

Cant we use sum( above) function here?

Let vStartDate = Floor(MakeDate(2017,1,1));

Let vEndDate = Floor(Today());

Let vDiff = vEndDate-vStartDate +1;

CALENDAR:

LOAD

Year & '' & Month As %YearMonth,

Year & '-' & Quarter As YearQuarter,

WeekYear & '-' & Num(Week, '00') As YearWeek;

LOAD

DateID,

Date(DateID) As DATE,

Year(DateID) As Year,

Num(Month(DateID), '00') As Month,

Month(DateID) As Monthh,

Day(DateID) As Day,

Week(DateID) As Week,

'Q' &Ceil(Month(DateID)/3) As Quarter,

WeekYear(DateID) As WeekYear,

-Year2Date(DateID) As YTD_Flag,

-Year2Date(DateID, -1) As LYTD_Flag,

weekday(DateID) AS Weekday,

autonumber(weekyear(DateID) &'|' & week(DateID), '_WeekSerial') AS _WeekSerial;

LOAD

RecNo()-1+$(vStartDate) As DateID

AutoGenerate($(vDiff));

Thanks

Neville

Re: Growth over two Years

Digvijay_Singh — Fri, 09 Mar 2018 17:16:18 GMT

Can you share expected output and sample data to see on which chart you are trying this.

Re: Growth over two Years

nevilledhamsiri — Fri, 09 Mar 2018 17:45:31 GMT

I have attached the excel & P/T done. Script too given

Thanks

Neville

Here it is a same file with data for two Years!

POLICY_REGISTER_2018:

LOAD BRANCH_CODE,

CLASS_CODE,

PICK(MATCH(CLASS_CODE,'MC','M4','M3')+1,'NON_MOTOR','MOTOR','MOTOR','MOTOR')AS MO_NM_SALES,

PRODUCT_CODE,

POL_POLICY_NO,

NAME,

REF_NO,

FROM_DATE,

END_DATE,

POL_SUM_INSURED,

TR_DATE,

MONTH(TR_DATE) AS TR_MONTH,

YEAR(TR_DATE)AS TR_YEAR,

TR_TYPE,

CP,

RS,

TC,

TOTAL,

MARKETING_PERSON,LEFT(MARKETING_PERSON,5) AS ME_CODE

FROM (ooxml, embedded labels, table is Sheet1);

Re: Growth over two Years

nevilledhamsiri — Fri, 09 Mar 2018 17:58:50 GMT

Shorten Excel attached for your ref

Please suggest the growth Jan_2018 vs Jan _2017 & same for Feb as well based on the following expressions

SUM({<MO_NM_SALES={'MOTOR'},ME_CODE>}TOTAL)

SUM({<MO_NM_SALES={'NON_MOTOR'},ME_CODE>}TOTAL)

SUM({<MO_NM_SALES={'MOTOR'},ME_CODE>}TOTAL)+SUM({<MO_NM_SALES={'NON_MOTOR'},ME_CODE>}TOTAL)

Thanks

Neville

Re: Growth over two Years

Digvijay_Singh — Fri, 09 Mar 2018 18:59:46 GMT

May be like this -

Re: Growth over two Years

nevilledhamsiri — Sat, 10 Mar 2018 01:02:12 GMT

Please send me the expression in a notepad for growth calculation. I am using a personnel edition.

Thanks

Neville

Re: Growth over two Years

nevilledhamsiri — Tue, 20 Mar 2018 17:42:19 GMT

If Year/ Month dimensions are arranged vertically, results re not produced on the given growth expression. How this could be corrected?

SUM({<MO_NM_SALES={'MOTOR'},BRANCH_CODE>}TOTAL)/ABOVE(SUM({<MO_NM_SALES={'MOTOR'},BRANCH_CODE>}TOTAL))-1

Regds

Neville

Re: Growth over two Years

nevilledhamsiri — Tue, 27 Mar 2018 17:32:33 GMT

Can some one look in o this!

Regds

Neville