Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

hschultz
Contributor

Running Total on specified fields

Hi guys

I need some assistance please. I am looking for the code in which i can achieve the following in my LOAD script:

My table gets extracted to something simular to this

CustomerProductDateTime
AngiaAAA01/01/20122
BulgionAAA05/01/20124
AngiaBBB10/01/20126
AngiaAAA15/01/20123
BulgionAAA20/01/20128
CarfieldCCC25/01/20124
BulgionAAA01/02/20124
BulgionAAA05/02/20123
CarfieldCCC10/02/20126
AngiaBBB15/02/20123
CarfieldCCC20/02/20123
AngiaAAA25/02/20129

So basically the table is sorted via Date. And if needed i am sure it can be Customer, then date.

Now the result i'm looking for is to have a running total of Time against Customer and Product, calculated per month. This must in accordance to date. So my result should look something like this:

CustomerProductDateTimeRunning Total
AngiaAAA01/01/201222
BulgionAAA05/01/201244
AngiaBBB10/01/201262
AngiaAAA15/01/201235
BulgionAAA20/01/2012812
CarfieldCCC25/01/201244
BulgionAAA01/02/201244
BulgionAAA05/02/201237
CarfieldCCC10/02/201266
AngiaBBB15/02/201233
CarfieldCCC20/02/201239
AngiaAAA25/02/201299

I was able to get nearly there by using this code:

IF(RecNo() = 1, Time,

    IF((Customer = Previous(Customer) AND Month = Previous(Month)), Time + Num(Previous(Peek(TimeRun))),Time)) AS TimeRun

But the problem is it has got to be sorted by costomer and date, but it also assumes it is always the same product. And that is where my error comes in.

It has to be sorted by date, so then the products would get mixed up and the string won't work.

PLEASE HELP!

1 Solution

Accepted Solutions

Re: Running Total on specified fields

Ok, change Customer&Product in Customer&Product&TS_Month

if(RowNo()=1 or Customer&Product&TS_Month<>previous(Customer&Product&TS_Month),Time,Time+peek(RunningTotal)) as RunningTotal


talk is cheap, supply exceeds demand
9 Replies
jvitantonio
Valued Contributor III

Re: Running Total on specified fields

Hi, is it an option for you to do this running sum in the script? When you use functions previous() this will take the previous physical row. This mean that if the user sort the table by another field, this formula will not work for you. I suggest to load the table and sorted the way you want it, and then reload it and apply this formula.

hschultz
Contributor

Re: Running Total on specified fields

Yes, this has to be run in the LOAD script.

But sorting via Customer > Date, means that Product is not sorted. And it cannot be sorted by product. Some customers' final combination of time total accross all products, where others combine time per product. But it must be worked out by date.

I can upload the concept (without various products) if that can maybe help make more sense?

jvitantonio
Valued Contributor III

Re: Running Total on specified fields

Yes please, it would be better. And include in the qvw file the desire result according to your data. (you can use a simple text box to do this)

Re: Running Total on specified fields

See attached example


talk is cheap, supply exceeds demand
hschultz
Contributor

Re: Running Total on specified fields

Please see what i have been working on, and that it works without product taken into account. (Each customer only has 1 product)

Basically it works out when the customer runs out of SLA hours, then from that point start with billing hours.

But i realised i need to take the Product into account also, so that is why i have this question.

*I know it is not exactly what you asked for, but it should clear things up

hschultz
Contributor

Re: Running Total on specified fields

That is close to what i'm looking for. But there is 1 issue:

On 01/02/2012, the time must reset and not continue with the previous runing total. So Bulgion must be 4, and not 4+12(from Jan)

Re: Running Total on specified fields

Ok, change Customer&Product in Customer&Product&TS_Month

if(RowNo()=1 or Customer&Product&TS_Month<>previous(Customer&Product&TS_Month),Time,Time+peek(RunningTotal)) as RunningTotal


talk is cheap, supply exceeds demand
hschultz
Contributor

Re: Running Total on specified fields

I was able to update your test script to include months:

if(RowNo()=1 or Customer&Product<>previous(Customer&Product) OR month(date(Date)) <> previous(month(date(Date))) ,Time,Time+peek(RunningTotal)) as RunningTotal

Resident Temp order by Customer,Product,Date;

And this appears that this will do the trick....

>As for the last comment, all you're saying is basically pop product in between customer & month?

I am in the proccess of pulling in live data so i will be able to give full confirmation when this is done

hschultz
Contributor

Re: Running Total on specified fields

Works like a charm. thank you

Community Browser