Running Total Issue

[arsal_90]

Hi Members,

Hope all doing well.

I have a data that have quantity product wise on data level. But issue is that i have to find the running total of quantity and every time when product change my running total counter is reset. In attachment you find the data in excel with the desired output.

[sunny_talwar]

Muhammad Arsalan Ashraf‌ brother recreating new threads won't help you. You need to explain your requirement in a better way ->  how are you trying to do the accumulate? I looked at your attached qvw and it is difficult to know what fields needs to be grouped on and which ones are used for check (KEY&[Product Description]). I think its either the grouping or this check which is screwing your results. If you give some basic understanding of what you doing, somebody here might be able to help you out.

Best,

Sunny

[arsal_90]

Sorting will be on Start date

[arsal_90]

Hi Sunindia,

Really Appreciate your kind help. It helps me out. Now i will check it throughly on myside and will let you know if there is any issue.

Thanks,

Arsalan

[sunny_talwar]

no problem

[arsal_90]

Actually Sunindia Problem is that sorting in qvd is not right. As you see in the Data.xlsx sorting is based on Product description and Start Date. So could please see that i think it might help you to identify my problem.

Really Appreciate your kind support.

Thanks

[svenkita]

Try This

[settu_periasamy · Accepted solution]

Hi,

you can try below script for order by the Product and start date using resident.

i used your 'Fact.qvd' file for reference.

Directory;
A:
LOAD [Customer #], 
     Company, 
     Facility, 
     [Item #], 
     Product, 
     [Order #], 
     [Start Date], 
     [Cancel Date], 
     [Entry Date], 
     Quantity, 
     Alloc, 
     [Picked Date], 
     Type, 
     [Customer Name]

FROM
Fact.qvd
(qvd);

Cumulative:
NoConcatenate
Load *,
  RowNo() as row,
  if(Previous(Product)=Product, rangesum(Quantity,peek(Cumulative)),Quantity) as Cumulative
  Resident A Order by Product asc, [Start Date] asc;


DROP Table A;

I just added the row field based on the Product and [Start Date] ascending. (Suppose if you mistakenly order the other field, again you can just order the row field)

In the Expression, just give sum(Cumulative)

Find the attachment for your reference.

Hope this helps.