HELP

[panipat1990]

Dear Team,

I stuck a requirement Kindly help me .

I Have a Sale data.i want to Caluclate some figure  ..

Header 1	Header 2	Header 3	Header 4	Header 5	Header 6
		TOP 50% volume contributors	Next 30%	Balance 20%
SUMMARY	TOTAL	A	B	C
MARKET TYP	20	3	5	12
MARKET SIZE	1000	500	300	200

HERE TOAL IS SHOWING TOTAL PERSON WHO SALED THINGS. AND SUM SALES

A:-SHOWING 50% SALE WITH COUNT OF THOSE PERSON WHO SALED 50% THNGS

B:-SHOWING 30% SALE WITH COUNT OF THOSE PERSON WHO ONLY SALED 30 %

C:-SAME SHOWING ONLY 20%

For this i am sharing data

[Digvijay_Singh]

Check this out, my results defer a bit from expected one, please check and revert -

Data:    //Load source data

LOAD Dearler,

    Sale

FROM

Testing_excel.xlsx

(ooxml, embedded labels, header is 4 lines, table is Sheet1);

Left join(Final)  //Calculate Total Sale Sum for further use

Load

  Sum(Sale) as TotalSale

Resident Final

;

Left join(Final)  //Calculate PercentSale

Load

  *,

  Sale/TotalSale as PercentSale

Resident Final;

NoConcatenate

Final1:  //Calculate sum of percents and sort records in descending order

Load *,

  rangesum(PercentSale,Peek(PercentSum)) as PercentSum

Resident Final

Order By Sale desc;

Drop table Data,Final;

[panipat1990]

Kindly tell me where is final table which using for left join purpose in script level

[panipat1990]

And Peek(PercentSum) How can i can use..PercentSum.Because  PercentSum is finding this formula

rangesum(PercentSale,Peek(PercentSum))

[Digvijay_Singh]

Didn't get you?

Initially PercentSum will be null, thats the reason I used rangesum, so the first value will be equal to PercentSale and it is assigned to PercentSum. From next row onwards, You get the PercentSum from the previous row through Peek function.

[Digvijay_Singh]

For first row, PercentSum -

rangesum(PercentSale,Peek(PercentSum))  means  rangesum(0.30,Null)  = 0.30

For second row -

rangesum(PercentSale,Peek(PercentSum))  means rangesum(0.44,0.30)  = 0.74

[Digvijay_Singh]

My bad, I made last minute change and made mistake -

Here is the updated script -

Data:    //Load source data

LOAD Dearler,

     Sale

FROM

Testing_excel.xlsx

(ooxml, embedded labels, header is 4 lines, table is Sheet1);

Left join(Data)   //Calculate Total Sale Sum for further use

Load

  Sum(Sale) as TotalSale

Resident Data

;

Left join(Data)  //Calculate PercentSale

Load

  *,

  Sale/TotalSale as PercentSale

Resident Data;

NoConcatenate

Final1:   //Calculate sum of percents and sort records in descending order

Load *,

  rangesum(PercentSale,Peek(PercentSum)) as PercentSum

Resident Data

Order By Sale desc;

Drop table Data;

[panipat1990]

But in script level where is final table. used left join final

[Digvijay_Singh]

Just responded on that with updated script.

[panipat1990]

Dear Sir,

If i do 50% of 16098 then it come 8049 but in above 7147 is coming.Kindly help me for resolving this.