Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
danette
Contributor III
Contributor III

Best way to manage data for trends

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

 

 

Labels (1)
6 Replies
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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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?