topic Re: Running Total on specified fields in QlikView

Running Total on specified fields

hschultz — Thu, 15 Nov 2012 10:17:17 GMT

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

Customer	Product	Date	Time
Angia	AAA	01/01/2012	2
Bulgion	AAA	05/01/2012	4
Angia	BBB	10/01/2012	6
Angia	AAA	15/01/2012	3
Bulgion	AAA	20/01/2012	8
Carfield	CCC	25/01/2012	4
Bulgion	AAA	01/02/2012	4
Bulgion	AAA	05/02/2012	3
Carfield	CCC	10/02/2012	6
Angia	BBB	15/02/2012	3
Carfield	CCC	20/02/2012	3
Angia	AAA	25/02/2012	9

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:

Customer	Product	Date	Time	Running Total
Angia	AAA	01/01/2012	2	2
Bulgion	AAA	05/01/2012	4	4
Angia	BBB	10/01/2012	6	2
Angia	AAA	15/01/2012	3	5
Bulgion	AAA	20/01/2012	8	12
Carfield	CCC	25/01/2012	4	4
Bulgion	AAA	01/02/2012	4	4
Bulgion	AAA	05/02/2012	3	7
Carfield	CCC	10/02/2012	6	6
Angia	BBB	15/02/2012	3	3
Carfield	CCC	20/02/2012	3	9
Angia	AAA	25/02/2012	9	9

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!

Re: Running Total on specified fields

jvitantonio — Thu, 15 Nov 2012 10:37:18 GMT

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.

Re: Running Total on specified fields

hschultz — Thu, 15 Nov 2012 10:43:16 GMT

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?

Re: Running Total on specified fields

jvitantonio — Thu, 15 Nov 2012 10:45:37 GMT

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

Gysbert_Wassenaar — Thu, 15 Nov 2012 10:53:29 GMT

See attached example

Re: Running Total on specified fields

hschultz — Thu, 15 Nov 2012 10:58:28 GMT

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

Re: Running Total on specified fields

hschultz — Thu, 15 Nov 2012 11:05:55 GMT

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

Gysbert_Wassenaar — Thu, 15 Nov 2012 11:14:40 GMT

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

Re: Running Total on specified fields

hschultz — Thu, 15 Nov 2012 11:24:52 GMT

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

Re: Running Total on specified fields

hschultz — Mon, 19 Nov 2012 08:56:31 GMT

Works like a charm. thank you