Accumulated Sale Month and Financial Year wise

[koushik_btech20]

I have a demo sales data Item,country,Financial Year and Month wise. I have to calculated the accumulated sale month and Financial Year wise inside the script and store the ultimate text file into txt or csv format Financial year wise(i.e. for Year 2013 file will be Sales2013_14.txt or Sales2013_14.csv).

Accumulated Sales means I have to show suppose ,

For a ITEM FGBRAMAB00001,

ITEM                              COUNTRY               MONTH               SALES          AccumulatedSales

FGBRAMAB00001           BBB                         Apr                      100               100

FGBRAMAB00001           BBB                         May                     150               250

If anyone have any solution please share here.

Regards

Koushik

[buzzy996]

try with this expression for accumulation,=sum([Sales Amount])+ RangeSum(Above(Total Sum([Sales Amount]),1,RowNo(TOTAL)-1))

[koushik_btech20]

But I have to do this inside the script not UI level. After loading the script it will show the accumulated figure month and financial year wise.

Regards

Koushik

[mrossoit]

If you think you have to do it inside the script only because you need to store the result you could try Shiva's solution and use macro to export UI object.

Regards

MR

[jvitantonio]

Hello Koushik,

This is how you can do it in the script:

myTable:

LOAD

if(ITEM = peek(ITEM) , RANGESUM(PEEK(SALES),SALES), SALES) as ACCUM, ITEM, COUNTRY, MONTH, SALES

inline

[

ITEM,                              COUNTRY,              MONTH,              SALES,         

FGBRAMAB00001,          BBB,                        Apr,                      100,             

FGBRAMAB00001,          BBB,                        May,                    150   

FGBRAMAB00002, AAA, Jan, 40

FGBRAMAB00002, AAA, Jan, 50

FGBRAMAB00002, CCC, Jan, 90

];   

store myTable into C:\myfile.txt (txt);

This script will only accumulate by ITEM. If you need it to accumulate by other field, you need to add and AND condition in the first part of the IF().EX:

if( ITEM = PEEK(ITEM) AND COUNTRY = PEEK(COUNTRY) ...

I hope this helps.

JV