topic Re: Rank a Time dimension by multiple dimensions in QlikView

Rank a Time dimension by multiple dimensions

Cmurphy55 — Sat, 16 Nov 2024 04:13:51 GMT

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?

Re: Rank a Time dimension by multiple dimensions

vishsaggi — Sat, 09 Mar 2019 04:27:33 GMT

What is your expected output?

Re: Rank a Time dimension by multiple dimensions

Cmurphy55 — Sun, 10 Mar 2019 18:24:02 GMT

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

Re: Rank a Time dimension by multiple dimensions

vishsaggi — Sun, 10 Mar 2019 18:56:59 GMT

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)

Re: Rank a Time dimension by multiple dimensions

Cmurphy55 — Tue, 12 Mar 2019 17:19:38 GMT

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