Rank a Time dimension by multiple dimensions

[Cmurphy55]

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?

[vishsaggi]

What is your expected output?

[Cmurphy55]

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 · Accepted solution]

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 · Accepted solution]

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];