Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Cmurphy55
Contributor II
Contributor II

Rank a Time dimension by multiple dimensions

I am trying to set up a dashboard that displays the start and end times for our dashboard runs. The problem is that we have dashboards that sometimes are run multiple times a day. I need add the option (via a button) to filter our line chart to display only the first run per day per dashboard.

 

See example below:

Dashboard_Name | Start_TimeDate           | End_TimeDate

                     xyz.qvw |  2019-03-07 07:21.12 | 2019-03-07 07:22.12    <--

                     xyz.qvw |  2019-03-08 07:21.12 | 2019-03-08 07:22.12    <--

                     xyz.qvw |  2019-03-08 08:34.01 | 2019-03-08 35:22.01

                     xyz.qvw |  2019-03-08 10:15.36 | 2019-03-08 10:16.36

                     xyz.qvw |  2019-03-09 07:21.12 | 2019-03-09 07:22.12    <--

 

I tried setting up a rank column in order to Identify the first run per day for each dashboard, but it's not working:

=AGGR(RANK(MIN(Start_TimeDate),1,2), Dashboard_Name)

 

How do I accomplish this on the UI side?

 

2 Solutions

Accepted Solutions
vishsaggi
Champion III
Champion III

Can you try this?
Script Solution:

LOAD *, Autonumber(DayID&StartDate,DayID&Dashboard_Name) AS Rank_Column;
Load *, TImestamp(Timestamp#(Start_TimeDate, 'YYYY-MM-DD hh:mm.ss'), 'YYYY-MM-DD hh:mm:ss') AS StartDate,
Day(TImestamp(Timestamp#(Start_TimeDate, 'YYYY-MM-DD hh:mm.ss'), 'YYYY-MM-DD hh:mm:ss')) AS DayID INLINE [
Dashboard_Name | Start_TimeDate| End_TimeDate
xyz.qvw | 2019-03-07 07:21.12 | 2019-03-07 07:22.12
abc.qvw | 2019-03-07 07:33.11 | 2019-03-07 07:34.12
abc.qvw | 2019-03-07 08:01.42 | 2019-03-07 08:03.01
xyz.qvw | 2019-03-08 07:21.12 | 2019-03-08 07:22.12
xyz.qvw | 2019-03-08 08:34.01 | 2019-03-08 35:22.01
xyz.qvw | 2019-03-08 10:15.36 | 2019-03-08 10:16.36
xyz.qvw | 2019-03-09 07:21.12 | 2019-03-09 07:22.12
] (delimiter is '|');

Then use this Rank_Column in your straight table.

OR use the same script with below as your expression in your straight table like

UI Solution:
= Aggr(Rank(-StartDate, 1,4), Dashboard_Name, DayID,StartDate)

View solution in original post

Cmurphy55
Contributor II
Contributor II
Author

I attempted your solution and it partially worked. The only problem is that my data contains more than one year, so instead, I used the FLOOR function instead of the date. I also was NOT able to get the script changes to work by utilizing an empty load statement like you did, instead, I had to set up my table as follows, which ended up working:

[Qlikview]:

LOAD @1 as Q_ID,
@2 as Dashboard_Name,
@3 as Start_TimeDate,
@4 as End_TimeDate,
@5 as Environment,

... some additional columns ...


Timestamp(Timestamp#(@3, 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD hh:mm:ss') AS StartDate,
FLOOR(Timestamp(Timestamp#(@3, 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD hh:mm:ss')) AS DayID,
Autonumber(FLOOR(Timestamp(Timestamp#(@3, 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD hh:mm:ss'))&Timestamp(Timestamp#(@3, 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD hh:mm:ss'),
FLOOR(Timestamp(Timestamp#(@3, 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD hh:mm:ss'))&@2)
AS Rank_Column
FROM
[location];

View solution in original post

4 Replies
vishsaggi
Champion III
Champion III

What is your expected output?
Cmurphy55
Contributor II
Contributor II
Author

An example output is displayed below. Essentially, I want an expression column that ranks each individual dashboard (on a per day basis) by the earliest time that was run. As you can see below, dashboard abc.qvw reloaded on the 7th of March 2 times. Dashboard xyz.qvw ALSO reloaded on March 7th, but ONLY 1 time. The rank column needs to be able to assign a value of 1 to the earliest run of EACH Dashboard PER DAY. That way on March 7th, xyz.qvw isn't given a rank of 1, and abc.qvw isn't given a rank of 2 and 3 that day for each of its respective runs.

 

I'm not sure how to set up the code for that in the expression field in my straight table.

 

Dashboard_Name | Start_TimeDate           | End_TimeDate            |  Rank_Column

                     xyz.qvw |  2019-03-07 07:21.12 | 2019-03-07 07:22.12  |  1

                    abc.qvw |  2019-03-07 07:33.11 | 2019-03-07 07:34.12  |  1

                    abc.qvw |  2019-03-07 08:01.42 | 2019-03-07 08:03.01  |  -     (or 2)

                     xyz.qvw |  2019-03-08 07:21.12 | 2019-03-08 07:22.12  |  1

                     xyz.qvw |  2019-03-08 08:34.01 | 2019-03-08 35:22.01  |  -     (or 2)

                     xyz.qvw |  2019-03-08 10:15.36 | 2019-03-08 10:16.36  |  -     (or 3)

                     xyz.qvw |  2019-03-09 07:21.12 | 2019-03-09 07:22.12  |  1

vishsaggi
Champion III
Champion III

Can you try this?
Script Solution:

LOAD *, Autonumber(DayID&StartDate,DayID&Dashboard_Name) AS Rank_Column;
Load *, TImestamp(Timestamp#(Start_TimeDate, 'YYYY-MM-DD hh:mm.ss'), 'YYYY-MM-DD hh:mm:ss') AS StartDate,
Day(TImestamp(Timestamp#(Start_TimeDate, 'YYYY-MM-DD hh:mm.ss'), 'YYYY-MM-DD hh:mm:ss')) AS DayID INLINE [
Dashboard_Name | Start_TimeDate| End_TimeDate
xyz.qvw | 2019-03-07 07:21.12 | 2019-03-07 07:22.12
abc.qvw | 2019-03-07 07:33.11 | 2019-03-07 07:34.12
abc.qvw | 2019-03-07 08:01.42 | 2019-03-07 08:03.01
xyz.qvw | 2019-03-08 07:21.12 | 2019-03-08 07:22.12
xyz.qvw | 2019-03-08 08:34.01 | 2019-03-08 35:22.01
xyz.qvw | 2019-03-08 10:15.36 | 2019-03-08 10:16.36
xyz.qvw | 2019-03-09 07:21.12 | 2019-03-09 07:22.12
] (delimiter is '|');

Then use this Rank_Column in your straight table.

OR use the same script with below as your expression in your straight table like

UI Solution:
= Aggr(Rank(-StartDate, 1,4), Dashboard_Name, DayID,StartDate)
Cmurphy55
Contributor II
Contributor II
Author

I attempted your solution and it partially worked. The only problem is that my data contains more than one year, so instead, I used the FLOOR function instead of the date. I also was NOT able to get the script changes to work by utilizing an empty load statement like you did, instead, I had to set up my table as follows, which ended up working:

[Qlikview]:

LOAD @1 as Q_ID,
@2 as Dashboard_Name,
@3 as Start_TimeDate,
@4 as End_TimeDate,
@5 as Environment,

... some additional columns ...


Timestamp(Timestamp#(@3, 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD hh:mm:ss') AS StartDate,
FLOOR(Timestamp(Timestamp#(@3, 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD hh:mm:ss')) AS DayID,
Autonumber(FLOOR(Timestamp(Timestamp#(@3, 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD hh:mm:ss'))&Timestamp(Timestamp#(@3, 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD hh:mm:ss'),
FLOOR(Timestamp(Timestamp#(@3, 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD hh:mm:ss'))&@2)
AS Rank_Column
FROM
[location];