Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Not applicable
‎2016-09-18 09:25 PM

Cumulative value on loading script with 3 dimension

Hi All,


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

I have the database as following.


   

MonthAccountIDDateVolume
Jan-1611/1/20160
Jan-1612/1/20160.5
Jan-1613/1/20161
Jan-1614/1/201620
Jan-1615/1/2016150
Jan-1621/1/20160
Jan-1622/1/201615
Jan-1623/1/201655
Jan-1624/1/2016250
Jan-1625/1/20160
Jan-1631/1/20160
Jan-1632/1/20160.1
Jan-1633/1/20160.9
Jan-1634/1/201690
Jan-1635/1/20162


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

  

MonthAccountIDDateVolumeCumulative_Vol
Jan-1611/1/201600
Jan-1612/1/20160.50.5
Jan-1613/1/201611.5
Jan-1614/1/20162021.5
Jan-1615/1/2016150171.5
Jan-1621/1/201600
Jan-1622/1/20161515
Jan-1623/1/20165570
Jan-1624/1/2016250320
Jan-1625/1/20160320
Jan-1631/1/201600
Jan-1632/1/20160.10.1
Jan-1633/1/201600.1
Jan-1634/1/20169090.1
Jan-1635/1/2016292.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.

PS

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

1,195 Views
0 Likes
1 Solution

Accepted Solutions
sunny_talwar
MVP
MVP
‎2016-09-23 03:29 AM

In response to

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;

View solution in original post

Community_233120_v1.qvw
975 Views
8 Replies
vishsaggi
Champion III
Champion III
‎2016-09-19 12:47 PM

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

];

975 Views
sunny_talwar
MVP
MVP
‎2016-09-19 10:12 PM

In response to vishsaggi

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;

Community_233120.qvw
975 Views
vishsaggi
Champion III
Champion III
‎2016-09-19 10:17 PM

In response to sunny_talwar

Thanks sunny this makes sense. Got it. 🙂

975 Views
Not applicable
‎2016-09-20 09:14 PM
Author

In response to sunny_talwar

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)

     

MonthDateAccountIDAmountVol
2016/022016/02/07100
2016/082016/08/28100
2016/082016/08/31100
2016/092016/09/01100
2016/092016/09/02144
2016/092016/09/04100
2016/092016/09/05100
2016/092016/09/06144
2016/092016/09/07144
2016/092016/09/08144
2016/092016/09/09144
2016/092016/09/11100
2016/092016/09/12144
2016/092016/09/13144
2016/092016/09/14100
2016/092016/09/15100
2016/092016/09/16144
2016/092016/09/17100
2016/092016/09/18100
2016/092016/09/19144
2016/092016/09/20144

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

975 Views
0 Likes
vishsaggi
Champion III
Champion III
‎2016-09-21 11:31 AM

In response to

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?

975 Views
0 Likes
Not applicable
‎2016-09-22 10:22 PM
Author

In response to vishsaggi

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

MonthAccountIDDateVolumeCumulative_Vol
2016/0812016/08/0140.040.0
2016/0812016/08/0220.560.5
2016/0812016/08/03150.2210.7
2016/0812016/08/04199.0409.7
2016/0812016/08/05490.0899.7
2016/0812016/08/060.0899.7
2016/0812016/08/07400.01,299.7
2016/0812016/08/080.01,299.7
2016/0812016/08/090.01,299.7
2016/0812016/08/10750.02,049.7
2016/0812016/08/11150.02,199.7
2016/0812016/08/12669.02,868.7
2016/0812016/08/130.02,868.7
2016/0812016/08/140.02,868.7
2016/0812016/08/15710.03,578.7
2016/0812016/08/1698.23,676.9
2016/0812016/08/172,670.26,347.1
2016/0812016/08/1815.56,362.6
2016/0812016/08/1955.36,417.9
2016/0812016/08/210.06,417.9
2016/0812016/08/226,021.012,438.9
2016/0812016/08/23950.013,388.9
2016/0812016/08/241,600.014,988.9
2016/0812016/08/251,200.016,188.9
2016/0812016/08/267,370.223,559.1
2016/0812016/08/28200.023,759.1
2016/0812016/08/294,400.428,159.5
2016/0812016/08/3070.328,229.8
2016/0812016/08/31130.028,359.8
2016/0912016/09/0115.015.0
2016/0912016/09/02950.0965.0
2016/0912016/09/040.0965.0
2016/0912016/09/052,380.03,345.0
2016/0912016/09/062,400.75,745.7
2016/0912016/09/07800.16,545.8
2016/0912016/09/082,300.28,846.0
2016/0912016/09/09200.09,046.0
2016/0912016/09/110.09,046.0
2016/0912016/09/125,400.814,446.8
2016/0912016/09/137,200.421,647.2
2016/0912016/09/14600.122,247.3
2016/0912016/09/157,000.529,247.8
2016/0912016/09/165,100.334,348.1
2016/0912016/09/170.034,348.1
2016/0912016/09/180.034,348.1
2016/0912016/09/190.034,348.1
2016/0912016/09/200.034,348.1
2016/0912016/09/210.034,348.1
2016/0822016/08/010.00.0
2016/0822016/08/0215.615.6
2016/0822016/08/03240.0255.6
2016/0822016/08/04473.6729.2
2016/0822016/08/05414.81,144.0
2016/0822016/08/060.01,144.0
2016/0822016/08/070.01,144.0
2016/0822016/08/08209.61,353.6
2016/0822016/08/09557.01,910.6
2016/0822016/08/10510.02,420.6
2016/0822016/08/11300.02,720.6
2016/0822016/08/12991.63,712.2
2016/0822016/08/130.03,712.2
2016/0822016/08/140.03,712.2
2016/0822016/08/151,138.04,850.2
2016/0822016/08/1698.04,948.2
2016/0822016/08/17971.85,920.0
2016/0822016/08/184,105.210,025.2
2016/0822016/08/1996.310,121.5
2016/0822016/08/210.010,121.5
2016/0822016/08/226,915.817,037.3
2016/0822016/08/2316.517,053.8
2016/0822016/08/245,814.422,868.2
2016/0822016/08/252,322.225,190.4
2016/0822016/08/2644.225,234.6
2016/0822016/08/280.025,234.6
2016/0822016/08/2915.025,249.6
2016/0822016/08/304.225,253.8
2016/0822016/08/311,001.426,255.2
2016/0922016/09/01600.0600.0
2016/0922016/09/020.0600.0
2016/0922016/09/040.0600.0
2016/0922016/09/053,328.03,928.0
2016/0922016/09/06673.64,601.6
2016/0922016/09/073,044.47,646.0
2016/0922016/09/08457.08,103.0
2016/0922016/09/09638.08,741.0
2016/0922016/09/11423.89,164.8
2016/0922016/09/12747.69,912.4
2016/0922016/09/1325.69,938.0
2016/0922016/09/14323.810,261.8
2016/0922016/09/150.010,261.8
2016/0922016/09/160.010,261.8
2016/0922016/09/170.010,261.8
2016/0922016/09/180.010,261.8
2016/0922016/09/190.010,261.8
2016/0922016/09/200.010,261.8
2016/0922016/09/210.010,261.8
975 Views
0 Likes
sunny_talwar
MVP
MVP
‎2016-09-23 03:29 AM

In response to

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;

Community_233120_v1.qvw
976 Views
Not applicable
‎2016-09-26 06:03 AM
Author

In response to sunny_talwar

Thank you Sunny!

That works!

Regards,

Haydock898989

975 Views
0 Likes