Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

berryandcherry6
Contributor II

Display first day counts hourly in line chart : Qliksense

Hi,

I want to display Opens count of First day records in each batch in a line chart Hourly. Below is my data

Table:

Load * inline

[

invitation,batchId, firstSendDate,opens,clientID

sdsd,23,'2018-03-22 01:24:23',1,1

2324dsf,23 , ,0,1

sdsd1,23,'2018-03-28 02:24:23',1,1

sdsd2,23,'2018-03-27 01:24:23',1,1

sdsd3,23,'2018-03-26 03:24:23',1,1

sdsd4,23,'2018-03-22 04:24:23',1,1

2324dsf,24, ,0,1

sdsd5,24,'2018-03-18 03:00:23',1,1

sdsd6,56,'2018-03-18 01:24:23',1,2

sdsd7,56,'2018-03-22 05:24:23',1,2

sdsd734,56,'2018-03-22 03:24:23',1,2

sdsd8,57,'2018-03-22 01:24:23',1,2

sdsd9,57,'2018-03-22 02:24:23',1,2

sdsd9,57,'2018-03-17 01:24:23',1,2

sdsd10,58,'2018-03-18 01:24:23',0,2

sdsd11,58,'2018-03-18 02:24:23',1,2

]


Its displaying like below, when i select for batchId = 23, which is wrong.

Capture.PNG

For batchID = 23, It has to display  open count = 2 at 2 hour. And it should not display other records in line graph as i have selected only one batch. Below is table explaining openscount for each batch.


ClientIDbatchIDmin dateOpens Count
12322/03/20182
12418/03/20181
25618/03/20181
25717/03/20181
25818/03/20181


Please help me on this.



Tags (1)
2 Replies
dwforest
Valued Contributor

Re: Display first day counts hourly in line chart : Qliksense

format to date hour in load script... Timestamp(firstSendDate, 'DD/HH/YYYY hh') as DateHour

MVP
MVP

Re: Display first day counts hourly in line chart : Qliksense

Hi,

You will want to ensure that the date is converted to a proper date (which it won't be by default coming from an inline) and then calculate the different date parts from that field, the code would be something like:

Table:

LOAD

     *,

     DayStart(DateTime), 'DD MMM YYYY') as Date,

     Hour(DateTime) as Hour,

     Minute(DateTime) as Minute,

     Month(DateTime) as Month,

     Date(MonthStart(DateTime), 'MMM-YY') as MonthYear

     ;

Load

     *,

     Date(Date#(firstSendDate, 'YYYY-MM-DD hh:mm:ss') as DateTime

     ;

inline

[

invitation,batchId, firstSendDate,opens,clientID

sdsd,23,'2018-03-22 01:24:23',1,1

]

;

Having these date parts created in the load script (and there are a lot more you can created besides) will make it easier to build your chart as you will have the dimensions without having to calculate them in the chart.

I don't quite follow what you are trying to achieve.  Is it that you only want to show the first value on any given day in the chart for a single batch?

If so you need to flag the first row, do this by creating a key in the load script, adding to the code above as follows:

Data:

LOAD

     batchId & '-' & Date(DateTime, 'YYMMDDhhmmss') as UniqueKey,

     *,

     DayStart(DateTime), 'DD MMM YYYY') as Date,

     etc...

Then attach to the first table a flag for when it is the first unique key for that batch:

LEFT JOIN (Data)

LOAD

     minstring(UniqueKey) as UniqueKey,

     1 as FirstInDay

RESIDENT Data

GROUP BY

     batchId

;

Once that has been done in the load script you can modify your chart expression to read:

sum({<FirstInDay={1}>}opens)

This will only include the first value in each day.

To get the first by various different combinations of dimensions, add the dimensions to the unique key and to the group by statement (comma separated).

Hope that makes sense, let me know how you get on.

Steve

Community Browser