topic Re: Help on Cumulative Sum in QlikView

Help on Cumulative Sum

bhupesh_gupta — Thu, 05 Oct 2017 13:09:01 GMT

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.

CreateDate	YOA	Key	Reference
01/01/2016	2016	ABC	123
01/01/2016	2016	ABC	234
01/02/2016	2016	ABC	123
01/02/2016	2016	XYZ	789
01/02/2016	2016	GHJ	458
01/03/2016	2016	TES	123

Output expected

CreateDate	Count	Cumulative Count
01/01/2016	2	2
01/02/2016	1	3
01/03/2016	2	5

Re: Help on Cumulative Sum

sunny_talwar — Thu, 05 Oct 2017 13:17:54 GMT

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

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

Re: Help on Cumulative Sum

bhupesh_gupta — Thu, 05 Oct 2017 13:30:06 GMT

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

Re: Help on Cumulative Sum

bhupesh_gupta — Fri, 06 Oct 2017 03:53:48 GMT

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

Re: Help on Cumulative Sum

sunny_talwar — Fri, 06 Oct 2017 11:13:12 GMT

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

Re: Help on Cumulative Sum

bhupesh_gupta — Fri, 06 Oct 2017 11:21:58 GMT

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

Re: Help on Cumulative Sum

sunny_talwar — Fri, 06 Oct 2017 11:26:49 GMT

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

The sortable Aggr function is finally here!

Re: Help on Cumulative Sum

bhupesh_gupta — Fri, 06 Oct 2017 11:28:30 GMT

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

Re: Help on Cumulative Sum

sunny_talwar — Fri, 06 Oct 2017 11:28:40 GMT

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....

Re: Help on Cumulative Sum

sunny_talwar — Fri, 06 Oct 2017 11:31:18 GMT

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))

Re: Help on Cumulative Sum

bhupesh_gupta — Fri, 06 Oct 2017 11:40:25 GMT

Hi Sunny

This worked ...

Thanks

Bhupesh