topic Re: Best way to manage data for trends in App Development

Best way to manage data for trends

danette — Tue, 30 Nov 2021 16:25:32 GMT

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

Re: Best way to manage data for trends

Nicole-Smith — Fri, 17 Sep 2021 23:18:43 GMT

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-in-Qlik/td-p/1121596

Re: Best way to manage data for trends

danette — Sat, 18 Sep 2021 05:57:40 GMT

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?

Re: Best way to manage data for trends

Nicole-Smith — Sat, 18 Sep 2021 17:32:32 GMT

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

Re: Best way to manage data for trends

danette — Sat, 18 Sep 2021 22:19:32 GMT

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.

Re: Best way to manage data for trends

Nicole-Smith — Sun, 19 Sep 2021 03:16:08 GMT

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.

Re: Best way to manage data for trends

danette — Mon, 20 Sep 2021 06:18:46 GMT

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?