Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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....
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))))
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
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 |
Just loaded your data in my app and it seems to be working well
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
It was a new feature which was introduced in QlikView 12.... read about it here
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
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....
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))