topic Re: Cumulative value on loading script with 3 dimension in QlikView

Cumulative value on loading script with 3 dimension

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have a difficulty to show cumulative figures on my QV dashboard.

I have the database as following.

Month	AccountID	Date	Volume
Jan-16	1	1/1/2016	0
Jan-16	1	2/1/2016	0.5
Jan-16	1	3/1/2016	1
Jan-16	1	4/1/2016	20
Jan-16	1	5/1/2016	150
Jan-16	2	1/1/2016	0
Jan-16	2	2/1/2016	15
Jan-16	2	3/1/2016	55
Jan-16	2	4/1/2016	250
Jan-16	2	5/1/2016	0
Jan-16	3	1/1/2016	0
Jan-16	3	2/1/2016	0.1
Jan-16	3	3/1/2016	0.9
Jan-16	3	4/1/2016	90
Jan-16	3	5/1/2016	2

Then I want to make a table with creating an additional column for 'Cumulative_Vol' as below .

Month	AccountID	Date	Volume	Cumulative_Vol
Jan-16	1	1/1/2016	0	0
Jan-16	1	2/1/2016	0.5	0.5
Jan-16	1	3/1/2016	1	1.5
Jan-16	1	4/1/2016	20	21.5
Jan-16	1	5/1/2016	150	171.5
Jan-16	2	1/1/2016	0	0
Jan-16	2	2/1/2016	15	15
Jan-16	2	3/1/2016	55	70
Jan-16	2	4/1/2016	250	320
Jan-16	2	5/1/2016	0	320
Jan-16	3	1/1/2016	0	0
Jan-16	3	2/1/2016	0.1	0.1
Jan-16	3	3/1/2016	0	0.1
Jan-16	3	4/1/2016	90	90.1
Jan-16	3	5/1/2016	2	92.1

Anybody can show me a simple resolution for this?

Moreover, I prefer to make that column by loading. (NOT expression on chart's measure)

The value on new column should be calculated by Month, Account ID, Date.

Advise with script(in text format) not adding sample qv files would be preferable due to my company's IT security reason.

Re: Cumulative value on loading script with 3 dimension

vishsaggi — Mon, 19 Sep 2016 16:47:43 GMT

Try this?

LOAD *,

IF(AccountID <> Previous(AccountID), Volume, Rangesum(Volume, peek('Vol'))) AS Vol;

LOAD * INLINE [

Month, AccountID, Date, Volume

Jan-16, 1, 1/1/2016, 0

Jan-16, 1, 2/1/2016, 0.5

Jan-16, 1, 3/1/2016, 1

Jan-16, 1, 4/1/2016, 20

Jan-16, 1, 5/1/2016, 150

Jan-16, 2, 1/1/2016, 0

Jan-16, 2, 2/1/2016, 15

Jan-16, 2, 3/1/2016, 55

Jan-16, 2, 4/1/2016, 250

Jan-16, 2, 5/1/2016, 0

Jan-16, 3, 1/1/2016, 0

Jan-16, 3, 2/1/2016, 0.1

Jan-16, 3, 3/1/2016, 0.9

Jan-16, 3, 4/1/2016, 90

Jan-16, 3, 5/1/2016, 2

];

Re: Cumulative value on loading script with 3 dimension

sunny_talwar — Tue, 20 Sep 2016 02:12:35 GMT

Vish -

Although this may work for the same, but keeping in mind that the actually data may or may not be sorted as we would want, it would be better to sort the data in the load script. This is contrary to what I just mention in another thread, but I think if in doubt if the sort order is appropriate or not, it would be best to sort before applying peek/previous logic.

Table:

LOAD * INLINE [

Month, AccountID, Date, Volume

Jan-16, 1, 1/1/2016, 0

Jan-16, 1, 2/1/2016, 0.5

Jan-16, 1, 3/1/2016, 1

Jan-16, 1, 4/1/2016, 20

Jan-16, 1, 5/1/2016, 150

Jan-16, 2, 1/1/2016, 0

Jan-16, 2, 2/1/2016, 15

Jan-16, 2, 3/1/2016, 55

Jan-16, 2, 4/1/2016, 250

Jan-16, 2, 5/1/2016, 0

Jan-16, 3, 1/1/2016, 0

Jan-16, 3, 2/1/2016, 0.1

Jan-16, 3, 3/1/2016, 0

Jan-16, 3, 4/1/2016, 90

Jan-16, 3, 5/1/2016, 2

];

FinalTable:

LOAD *,

If(AccountID <> Previous(AccountID), Volume, Rangesum(Volume, peek('Vol'))) AS Vol

Resident Table

Order By AccountID, Date;

DROP Table Table;

Re: Cumulative value on loading script with 3 dimension

vishsaggi — Tue, 20 Sep 2016 02:17:37 GMT

Thanks sunny this makes sense. Got it. 🙂

Re: Cumulative value on loading script with 3 dimension

Wed, 21 Sep 2016 01:14:36 GMT

Hi Both,

Thank you for your advises.

I tried the script you provided me and found it works but it does not adopted to my database.

I could not make the column as I expected. (Should be due to my bad explanation...)

The result is as below: (Just I exported 1 account as a sample)

Month	Date	AccountID	Amount	Vol
2016/02	2016/02/07	1	0	0
2016/08	2016/08/28	1	0	0
2016/08	2016/08/31	1	0	0
2016/09	2016/09/01	1	0	0
2016/09	2016/09/02	1	4	4
2016/09	2016/09/04	1	0	0
2016/09	2016/09/05	1	0	0
2016/09	2016/09/06	1	4	4
2016/09	2016/09/07	1	4	4
2016/09	2016/09/08	1	4	4
2016/09	2016/09/09	1	4	4
2016/09	2016/09/11	1	0	0
2016/09	2016/09/12	1	4	4
2016/09	2016/09/13	1	4	4
2016/09	2016/09/14	1	0	0
2016/09	2016/09/15	1	0	0
2016/09	2016/09/16	1	4	4
2016/09	2016/09/17	1	0	0
2016/09	2016/09/18	1	0	0
2016/09	2016/09/19	1	4	4
2016/09	2016/09/20	1	4	4

Seems 'Vol' is not cumulative number on my database.
I am wondering if it is caused by how Database keeps the information for 'Month' information.

Could you provide me an additional suggestion to resolve?

Thanks,

Haydock898989

Re: Cumulative value on loading script with 3 dimension

vishsaggi — Wed, 21 Sep 2016 15:31:32 GMT

So what is the expected output you are looking here? It is just displaying the Amount in Vol column! Why you want to display same information in two fields? Just wondering to know what you expected output?

Re: Cumulative value on loading script with 3 dimension

Fri, 23 Sep 2016 02:22:27 GMT

Hi Vish,

Apologies for insufficient information and made you waste your time.

Below is the expected output. Pls check following comments as well.

Comments:

- Would like to create column e with using column a, b, c, d

- Column e should be the Cumulative Value by Month, AccountID and Date. Besides pls be aware that it will start calculate from 0 when Month or AccountID changed. (Pls see the bold value)

Thanks,

Haydock898989

a, b, c, d, e

Month	AccountID	Date	Volume	Cumulative_Vol
2016/08	1	2016/08/01	40.0	40.0
2016/08	1	2016/08/02	20.5	60.5
2016/08	1	2016/08/03	150.2	210.7
2016/08	1	2016/08/04	199.0	409.7
2016/08	1	2016/08/05	490.0	899.7
2016/08	1	2016/08/06	0.0	899.7
2016/08	1	2016/08/07	400.0	1,299.7
2016/08	1	2016/08/08	0.0	1,299.7
2016/08	1	2016/08/09	0.0	1,299.7
2016/08	1	2016/08/10	750.0	2,049.7
2016/08	1	2016/08/11	150.0	2,199.7
2016/08	1	2016/08/12	669.0	2,868.7
2016/08	1	2016/08/13	0.0	2,868.7
2016/08	1	2016/08/14	0.0	2,868.7
2016/08	1	2016/08/15	710.0	3,578.7
2016/08	1	2016/08/16	98.2	3,676.9
2016/08	1	2016/08/17	2,670.2	6,347.1
2016/08	1	2016/08/18	15.5	6,362.6
2016/08	1	2016/08/19	55.3	6,417.9
2016/08	1	2016/08/21	0.0	6,417.9
2016/08	1	2016/08/22	6,021.0	12,438.9
2016/08	1	2016/08/23	950.0	13,388.9
2016/08	1	2016/08/24	1,600.0	14,988.9
2016/08	1	2016/08/25	1,200.0	16,188.9
2016/08	1	2016/08/26	7,370.2	23,559.1
2016/08	1	2016/08/28	200.0	23,759.1
2016/08	1	2016/08/29	4,400.4	28,159.5
2016/08	1	2016/08/30	70.3	28,229.8
2016/08	1	2016/08/31	130.0	28,359.8
2016/09	1	2016/09/01	15.0	15.0
2016/09	1	2016/09/02	950.0	965.0
2016/09	1	2016/09/04	0.0	965.0
2016/09	1	2016/09/05	2,380.0	3,345.0
2016/09	1	2016/09/06	2,400.7	5,745.7
2016/09	1	2016/09/07	800.1	6,545.8
2016/09	1	2016/09/08	2,300.2	8,846.0
2016/09	1	2016/09/09	200.0	9,046.0
2016/09	1	2016/09/11	0.0	9,046.0
2016/09	1	2016/09/12	5,400.8	14,446.8
2016/09	1	2016/09/13	7,200.4	21,647.2
2016/09	1	2016/09/14	600.1	22,247.3
2016/09	1	2016/09/15	7,000.5	29,247.8
2016/09	1	2016/09/16	5,100.3	34,348.1
2016/09	1	2016/09/17	0.0	34,348.1
2016/09	1	2016/09/18	0.0	34,348.1
2016/09	1	2016/09/19	0.0	34,348.1
2016/09	1	2016/09/20	0.0	34,348.1
2016/09	1	2016/09/21	0.0	34,348.1
2016/08	2	2016/08/01	0.0	0.0
2016/08	2	2016/08/02	15.6	15.6
2016/08	2	2016/08/03	240.0	255.6
2016/08	2	2016/08/04	473.6	729.2
2016/08	2	2016/08/05	414.8	1,144.0
2016/08	2	2016/08/06	0.0	1,144.0
2016/08	2	2016/08/07	0.0	1,144.0
2016/08	2	2016/08/08	209.6	1,353.6
2016/08	2	2016/08/09	557.0	1,910.6
2016/08	2	2016/08/10	510.0	2,420.6
2016/08	2	2016/08/11	300.0	2,720.6
2016/08	2	2016/08/12	991.6	3,712.2
2016/08	2	2016/08/13	0.0	3,712.2
2016/08	2	2016/08/14	0.0	3,712.2
2016/08	2	2016/08/15	1,138.0	4,850.2
2016/08	2	2016/08/16	98.0	4,948.2
2016/08	2	2016/08/17	971.8	5,920.0
2016/08	2	2016/08/18	4,105.2	10,025.2
2016/08	2	2016/08/19	96.3	10,121.5
2016/08	2	2016/08/21	0.0	10,121.5
2016/08	2	2016/08/22	6,915.8	17,037.3
2016/08	2	2016/08/23	16.5	17,053.8
2016/08	2	2016/08/24	5,814.4	22,868.2
2016/08	2	2016/08/25	2,322.2	25,190.4
2016/08	2	2016/08/26	44.2	25,234.6
2016/08	2	2016/08/28	0.0	25,234.6
2016/08	2	2016/08/29	15.0	25,249.6
2016/08	2	2016/08/30	4.2	25,253.8
2016/08	2	2016/08/31	1,001.4	26,255.2
2016/09	2	2016/09/01	600.0	600.0
2016/09	2	2016/09/02	0.0	600.0
2016/09	2	2016/09/04	0.0	600.0
2016/09	2	2016/09/05	3,328.0	3,928.0
2016/09	2	2016/09/06	673.6	4,601.6
2016/09	2	2016/09/07	3,044.4	7,646.0
2016/09	2	2016/09/08	457.0	8,103.0
2016/09	2	2016/09/09	638.0	8,741.0
2016/09	2	2016/09/11	423.8	9,164.8
2016/09	2	2016/09/12	747.6	9,912.4
2016/09	2	2016/09/13	25.6	9,938.0
2016/09	2	2016/09/14	323.8	10,261.8
2016/09	2	2016/09/15	0.0	10,261.8
2016/09	2	2016/09/16	0.0	10,261.8
2016/09	2	2016/09/17	0.0	10,261.8
2016/09	2	2016/09/18	0.0	10,261.8
2016/09	2	2016/09/19	0.0	10,261.8
2016/09	2	2016/09/20	0.0	10,261.8
2016/09	2	2016/09/21	0.0	10,261.8

Re: Cumulative value on loading script with 3 dimension

sunny_talwar — Fri, 23 Sep 2016 07:29:41 GMT

Try this:

Table:

LOAD Date(Date#(Month, 'YYYY/MM'), 'YYYY/MM') as Month,

AccountID,

Date,

Volume

FROM

[https://community.qlik.com/thread/233120]

(html, codepage is 1252, embedded labels, table is @4);

FinalTable:

LOAD *,

If(AccountID <> Previous(AccountID) or Month <> Previous(Month), Volume, Rangesum(Volume, peek('Vol'))) AS Vol

Resident Table

Order By AccountID, Month, Date;

DROP Table Table;

Re: Cumulative value on loading script with 3 dimension

Mon, 26 Sep 2016 10:03:20 GMT

Thank you Sunny!

That works!

Regards,

Haydock898989