Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bhupesh_gupta
Creator
Creator

Help on Cumulative Sum

Hello All,

I need to create a straight table where Createdate will be Dimention and count of records should be the expression , but I have to count records based on a unique combination of YOA,Key and Reference.

So if a unique record is count in first month , it should not be counted in the consecutive months . Below is the sample data .

Kindly help. 

CreateDateYOAKeyReference
01/01/20162016ABC123
01/01/20162016ABC234
01/02/20162016ABC123
01/02/20162016XYZ789
01/02/20162016GHJ458
01/03/20162016TES123

Output expected

   

CreateDateCountCumulative Count
01/01/201622
01/02/201613
01/03/201625
1 Solution

Accepted Solutions
sunny_talwar

Alternative would to be to sort your CreatDate field in the script as Aggr() sorts by load order by default... so if you create date will be correctly sorted in the script... it will not need the sortable Aggr() function.... but make sure that the sorting is done in the database itself... because sort order is determined by the first time a field is loaded in....

View solution in original post

10 Replies
sunny_talwar

How are you getting 2 for 01/02/2016? and 1 for 01/03/2016? Is it a typo.... I get this

Capture.PNG

=Sum(Aggr(If(YOA&Key&Reference <> Above(TOTAL YOA&Key&Reference), 1, 0), YOA, Key, Reference, (CreateDate, (numerical))))

bhupesh_gupta
Creator
Creator
Author

Hi Sunny

Thanks for the quick response ,

Yes it's a typo , the second last record should be marked with a created date 01/03/2016.

Let me use this logic and will get back

Regards'

Bhupesh

bhupesh_gupta
Creator
Creator
Author

Hi Sunny

I tried your expression , it's working fine with the sample data , but when I use same on the real data , result is not what is expected .

See the attached scrambled data which I am using in my project.

Result expected in cumulative sum is

 

01/01/2016

1

01/08/2016

993

01/09/2016

2,481

01/10/2016

3,857

01/11/2016

5,196

01/12/2016

6,251

01/01/2017

7,216

01/02/2017

8,362

01/03/2017

9,986

01/04/2017

11,054

01/05/2017

12,469

01/06/2017

14,393

01/07/2017

15,854

 

sunny_talwar

Just loaded your data in my app and it seems to be working well

Capture.PNG

bhupesh_gupta
Creator
Creator
Author

Hi Sunny

I can see that you have used Numerical in the expression. What that part is doing?

As at my end it's not working because of that , and as soon as I remove Numerical I get the result but worng one.

Regards

Bhupesh

sunny_talwar

It was a new feature which was introduced in QlikView 12.... read about it here

The sortable Aggr function is finally here!

bhupesh_gupta
Creator
Creator
Author

Ahh .. but our local machine as well as servers are on 11.20 SR10.

Do we have any work around to achieve the same in lower version

sunny_talwar

Alternative would to be to sort your CreatDate field in the script as Aggr() sorts by load order by default... so if you create date will be correctly sorted in the script... it will not need the sortable Aggr() function.... but make sure that the sorting is done in the database itself... because sort order is determined by the first time a field is loaded in....

sunny_talwar

Here is a way to fix the sorting

Table:

LOAD YOA,

    Key,

    Reference,

    CreateDate

FROM

[..\..\Downloads\TEST DATA - Copy.xlsx]

(ooxml, embedded labels, table is Sheet3);

Table2:

LOAD *,

CreateDate as CreateDateTemp

Resident Table

Order By CreateDate;

DROP Table Table;

Now use CreateDateTemp in your Aggr() expression

=Sum(Aggr(If(YOA&Key&Reference <> Above(TOTAL YOA&Key&Reference), 1, 0), YOA, Key, Reference, CreateDateTemp))