Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
I need some advice on how it is better to proceed in my situation.
I have a big database where I have person id, birthdate
I would like to create a trend chart where I have the count of people being 1 year old day by day ( date on x-axis and the count of people being 1 year old on Y axis, consider this example for simplicity). In such situation I would make a calendar using a loop for each person, where starting from the birthdate I would iterate until the current date, in order to have a table where i have for each person and each day, his age.
id , birthdate, date, age
A, 01/02/2021, 01/02/2021, 0
A, 01/02/2021, 02/02/2021, 0
[...]
A, 01/02/2021, 01/03/2021, 1
A, 01/02/2021, 02/03/2021, 1
A, 01/02/2021, 03/03/2021, 1
A, 01/02/2021, 04/03/2021, 1
[...]
But this loop would take a lot of time considering the dimension of the database.
How would you manage this need?
Thank you
Instead of editing your data, it sounds like you could just use a cumulative sum.
This post has an example of how to do that (but there are many other posts on the community discussing the same): https://community.qlik.com/t5/New-to-Qlik-Sense/Is-it-possible-to-create-a-cumulative-sum-line-graph...
Hello Nicole-Smith,
thank you for your suggestion, but I still have the problem about how to build the etl behind.
To use rangesum and above, I should be able to count the 1 year old people existing every day, and with the original data where I have only the birthday, this can't be done as far as I know, if I don't build some kind of huge calendar as I said in my first post. Maybe can it directly be doe with a formula inside the graph? But how?
The rangesum/above you would do on the chart itself. If you need to create a calendar in the script, there are a lot of posts on how to do that as well: https://community.qlik.com/t5/QlikView-App-Dev/Creating-A-Master-Calendar/td-p/341286
I know I can create a master calendar as I said in the first post, but considering that I should make a long loop for a big number of records, this would take a lot of time. I'm not using qvds.
So I know how to do this chart in a standard way, but I wanted to know if there is an alternative way, without creating in the etl the historic data where I would have for each day, each person with his age.
If you keep the master calendar as a separate table, you don't need to duplicate your rows of data, which should help with size/performance.
If you use a continuous axis, you may be able to just use the rangesum/above on the chart without needing the master calendar at all. I'm not 100% sure as I haven't tested.
This is what I would like to do (not to duplicate the rows), but I wouldn't know how to do it inside the graph. What measure to use? How can I count how many people are 1 year old each day, if I have only the information of his/fer datebirth?