Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
DiegoLotti
Contributor III
Contributor III

Best data model for finite states date transitions

Hello, I would like to visualize the number of documents in a particular state by date.

My documents have daily changes to their states and I have a log table with the start date & end date (null if this is the current state).
The table structure is this:

 
fileIdstatestart dateend date
1000-1STARTDATE01/01/2020 08:29
1000001/01/2020 08:2903/01/2020 12:15
1000103/01/2020 12:1506/01/2020 14:17
1000206/01/2020 14:1707/01/2020 10:44
1000307/01/2020 10:44NULL
2000-1STARTDATE01/01/2020 11:07
2000001/01/2020 11:0702/01/2020 17:13
2000102/01/2020 17:1304/01/2020 14:17
2000204/01/2020 14:1705/01/2020 10:44
2000305/01/2020 10:44NULL

 

I would like to write a measure that counts all the files that were in a particular state, for each date value.
I will filter state=1 (for example) and I would like a chart which shows the number of files in state 1 at 01/01/2020, 02/01/2020, ... and so on. I need to visualize the time trend of the various states.

Can I write a measure or it's better to calculate the scalar product of document states and calendar dates ?

In SQL I would join calendar table with document state table ON calendar.date between documentState.startDate AND ISNULL(documentState.endDate, GETDATE()). 

Can you help ?

 

 

0 Replies