Opening and closing qty and value ..

[#1]

Hi All,

Want to Calculate opening and closing qty and values.

PFA Excel for the reference.

Thanks .

[vikasmahajan]

Try this

vikas

[#3]

HI Vikas ,

i tried the below mentioned  code  in back end ...but unable to solve this out.

PFA Qvw n output excel ..its urgent

InputTemp:

//load *, Amount/Qty as Issued;

LOAD   Section,

     TransDate,

     Amount,

    if(Qty>0,Qty) AS [Recieved Qty],

   if(Qty<0,Qty) AS [Issued Qty]

FROM

C:\Users\ABHIJEET\Desktop\closing_Opening_Final.xlsx

(ooxml, embedded labels, table is Sheet1) ;

Input:

Load

  Section,

     TransDate,

     sum(Amount) AS [Balance Amount],

     sum([Recieved Qty])+sum([Issued Qty]) AS [Balance Qty],

     SUM([Recieved Qty]) AS [Recieved Qty],

     SUM([Issued Qty]) AS [Issued Qty]

     Resident InputTemp Group by Section,TransDate;

     drop table InputTemp;

Input_1:

load *,ClosingAmount/Total_Qty as Per_issue_Value,

ClosingAmount/Total_Qty *[Issued Qty]*(-1) as Value_Qty,

(ClosingAmount-ClosingAmount/Total_Qty *[Issued Qty]*(-1)),0+(ClosingAmount-ClosingAmount/Total_Qty *[Issued Qty]*(-1)) as Closing_Amount;

Load Section,

     TransDate,

rangesum([Issued Qty],0) as [Issued Qty],

rangesum([Balance Amount]) as [Balance Amount],

rangesum([Recieved Qty],0) as [Recieved Qty],

if(peek(Section)=Section,peek(ClosingQty),0) as OpeningQty,

if(peek(Section)=Section, rangesum(peek(ClosingQty),[Balance Qty]),[Balance Qty]) as ClosingQty,

// rangesum(Qty,0) as IssueQty,

//if(Peek(Section)= Section, rangesum(peek(Amount))) as Opening_Amount,

//if(Peek(Section)= Section, rangesum(peek(Opening_Amount1),0)) as Opening_Amount1

//if(peek(Section)=Section,Peek(ClosingAmount),0)as OpeningAmount,

//

  if(peek(Section)=Section,peek(ClosingAmount),0) as OpeningAmount,

  if(peek(Section)=Section,rangesum(peek(ClosingAmount),[Balance Amount]),[Balance Amount]) AS ClosingAmount,

  if(peek(Section)=Section,peek(ClosingQty),0)+rangesum([Recieved Qty],0) as Total_Qty

// peek(ClosingQty)/if(peek(Section)=Section,peek(ClosingQty),0)+rangesum([Recieved Qty],0) as Final_Opening

  // (if(peek(Section)=Section,rangesum(peek(ClosingAmount),Amount),Amount)/ if(peek(Section)=Section,rangesum(peek(Qty),Qty),Qty))*((Qty)) as   PerIssueQty

resident Input order by TransDate Asc;

DROP table Input;

thanks