Best way to manage data for trends

[danette]

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

[Nicole-Smith]

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

[danette]

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?

[Nicole-Smith]

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

[danette]

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.

[Nicole-Smith]

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.

[danette]

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?