Qlik Community

Ask a Question

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor
Contributor

QlikView Running PivotTable

How can i get the below desired result in a pivot table. My fact table only has in-time and Out-time. But, I need to count the cases along the duration of the case.

  

  

Saturday - Rooms Running
Date Total Cases 7:157:307:458:008:158:308:459:009:159:309:4510:0010:1510:3010:4511:0011:1511:3011:4512:0012:1512:3012:4513:0013:1513:3013:4514:0014:1514:3014:4515:0015:1515:3015:4516:0016:1516:3016:4517:00
6/30/2018        8        -          -           1         1         1         2         2         2         1         1         1         1         2         2         2         1         1         1         2         2         2         2         2         1         1        -          -          -          -          -          -          -          -           1         1         1         1         1        -          -  
6/23/2018        8         1        -           1        -          -          -          -           1         2         2         2         2         2         1        -          -           1         2         2         2         1         1         1         1         1        -          -          -          -           1         1         1         1         1         1         1         1         1         1         1
6/16/2018        8        -          -           1         1         1         1         1         1         1         1         2         1         1         1        -          -          -          -           1         1         1        -          -           1         1         1         1         1         1         1         1         1         1         1        -          -           1         1         1         1
6/9/2018        7        -          -           2         1         1         1         1        -           1         1         1         1         1         1         1         1         1         1         2         2         2         2         2         2         2         2         2         2        -          -          -          -          -           1         1         1         1         1         1         1
6/2/2018        3        -          -           1         1         1         1         1        -          -          -           1         1         1         1         1         1         1         1         1         1        -          -          -          -          -          -          -          -          -          -          -          -          -          -          -          -          -          -          -          -  
5/26/2018        6        -          -           1         1         1         1         1         1         1         1         1        -          -           1         1         1         1         1         1         2         2         2         1         1         1         1         1         1         1         2         2         2         2         2         2         2         2         2         2         2

FACT
DateCaseInTime Out Time
06/30/2018A7:3310:52
06/30/2018A8:169:01
06/30/2018B10:1012:53
06/30/2018C10:3523:03
06/30/2018C11:3212:12
06/30/2018A12:0212:46
06/30/2018D12:4013:24
06/30/2018C15:1916:32
06/30/2018B21:4122:24
Time Dim
TimeHourMin
7:0070
7:0171
7:0272
7:0373
7:0474
7:0575
7:0676
7:0777
7:0878
7:0979
7:10710
7:11711
7:12712
7:13713
7:14714

Appreciate your response

1 Solution

Accepted Solutions
Partner
Partner

Not the prettiest approach, but see the attached file and QVW for a solution.

I've altered your dimension and used intervalmatch to create each individual time line needed.

View solution in original post

7 Replies
Partner
Partner

Whats the logic behind the count?

If there's a class running from 9:03 to 9:33 it should be counted as four periods (9h~9:15;9:16~9:30;9:31~9:35)?

Contributor
Contributor

it should be counted as 3 periods (9:15, 9:30 & 9:45).


Logic is basically the count(cases) by time slot duration (OutTime - InTime).

Partner
Partner

Not the prettiest approach, but see the attached file and QVW for a solution.

I've altered your dimension and used intervalmatch to create each individual time line needed.

View solution in original post

Partner
Partner

See the attached QVW i sent for a possible solution.

Contributor
Contributor

Thanks Felip. This is a workable solution and really appreciate your help. I am currently modifying my data sets to make them fall in line with your model. I will update you soon.

Contributor
Contributor

Felip, this solution worked, appreciate it.

Partner
Partner

Glad it helped .