Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have following data:
I created this table in QlikView. Duration and Maximum columns are in "hh:mm" format.
"Utilization" column = "Duration" divided by "Maximum" in percentage for each row.
Table1:
Key | Date | Duration | Maximum | Utilization |
1 | 06/26/2013 | 0:26 | 0:36 | 72% |
1 | 06/27/2013 | 2:56 | 10:26 | 28% |
1 | 06/28/2013 | 2:30 | 9:39 | 26% |
2 | 06/26/2013 | 0:26 | 0:36 | 72% |
2 | 06/27/2013 | 2:55 | 10:26 | 28% |
2 | 06/28/2013 | 1:40 | 9:39 | 17% |
3 | 06/26/2013 | 0:26 | 0:36 | 72% |
3 | 06/27/2013 | 1:40 | 10:26 | 16% |
3 | 06/28/2013 | 2:05 | 9:39 | 22% |
4 | 06/26/2013 | 0:25 | 0:36 | 69% |
4 | 06/27/2013 | 1:40 | 10:26 | 16% |
4 | 06/28/2013 | 1:40 | 9:39 | 17% |
I want to take the sum of column "Duration" for each "Key" across three dates and then divide the result by sum of all values in the "Maximum" column for each key across all dates.
The result must look like this:
Table2:
Key | DurationSum | MaximumSum | UtilizationSum |
1 | 5:52 | 20:41 | 28.36% |
2 | 5:01 | 20:41 | 24.25% |
3 | 4:11 | 20:41 | 20.23% |
4 | 3:45 | 20:41 | 18.13% |
where "DurationSum" column is sum of first three rows from "Table1" for "Key 1" and "MaximumSum" is sum of first three rows for "Key 1" from "Table1". UtilizationSum is DurationSum divided by MaximumSum
How do I get Table2
I am surprised that I cannot get QlikView to do this either as an Expression in the Chart or in the Table. I tried average. I even tried creating a variable in the Load script.
Seems simple. Can someone please guide?
Thanks,
Make a chart with Key as dimension, and the following three expressions:
Sum(Duration)
Sum(Maximum)
Sum(Duration) / Sum(Maximum)
HIC
Make a chart with Key as dimension, and the following three expressions:
Sum(Duration)
Sum(Maximum)
Sum(Duration) / Sum(Maximum)
HIC
Hi Henric,
Thanks for the quick response. I have used "Key" and "Date" as Dimensions
"Duration", "Maximum", and "Utilization" are Expressions.
I am currently using Duration = Interval(Sum(EndDate - StartDate),'hh:mm'). Maximum value is derived using a complicated expression. I did not use Sum(Maximum).
It worked.
Thanks,