Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

berryandcherry6
Contributor II

Count of Min(Date) in line graph is not working in Qliksense

Hi,

i have table with below fields,

LOAD `invitation_id`,

    `client_id`,

    `batch_meta_data_id`,

    first_email_send_date, // timestamp

    date(floor(first_email_send_date),'MM-DD-YYYY') as formatted_send_date,

   opened ;

from INVITATION table;

so to get counts of opens for particular batch_meta_data_id for min(first_email_send_date). i am using like below

tried these expressions:

count({$<batch_meta_data_id={'96'}, formatted_send_date= {"=$(=Min(formatted_send_date))"},opened = {'1'}>}distinct invitation_id)

count({$<batch_meta_data_id={'96'}, formatted_send_date= {"=$(=Date(Min(formatted_send_date),'MM-DD-YYYY'))"},opened = {'1'}>}distinct invitation_id)


Dimension: Hour(first_opened_date)


But i am getting Zero.

What am i doing wrong. Please help me.

1 Solution

Accepted Solutions

Re: Count of Min(Date) in line graph is not working in Qliksense

Use Dimension

=Hour(first_opened_date)

Expression

COUNT(

Distinct

    Aggr(

        IF(

            formatted_send_date = Min(Total <batch_meta_data_id>formatted_send_date)

                and

            Floor(first_opened_date) = formatted_send_date

            ,invitation_id)

        ,formatted_send_date,invitation_id,batch_meta_data_id)

)



I can see the total is not 1048...

Can you please verify?


Also, You don't have to use opened = 1 because if we are talking about same formatted and opened date then you have no opened date where opened = 0.


Correct me if I am wrong..!!

18 Replies

Re: Count of Min(Date) in line graph is not working in Qliksense

What is the dimension you are using?

Re: Count of Min(Date) in line graph is not working in Qliksense

or try this

=FirstSortedValue({<batch_meta_data_id={'96'}>}

Aggr(Count({<batch_meta_data_id={'96'}>}Distinct invitation_id),batch_meta_data_id,formatted_send_date),

-Aggr(formatted_send_date, batch_meta_data_id,formatted_send_date)

)

berryandcherry6
Contributor II

Re: Count of Min(Date) in line graph is not working in Qliksense

Dimension: Hour(first_opened_date)

berryandcherry6
Contributor II

Re: Count of Min(Date) in line graph is not working in Qliksense

Hi mrkachhiaimp

i followed your code but it still shows wrong values for min date.

for

batch_meta_data_id = 96, total open counts  = 1048

batch_meta_data_id = 97, total open counts  = 357



I am attaching sample app.

Re: Count of Min(Date) in line graph is not working in Qliksense

What is the output you are looking for?

berryandcherry6
Contributor II

Re: Count of Min(Date) in line graph is not working in Qliksense

I am looking for a hourly open stats for first day of the batch in line graph.

Re: Count of Min(Date) in line graph is not working in Qliksense

I am talking about output.. Not description..

give me number for particular case, so that I can try to match that.

berryandcherry6
Contributor II

Re: Count of Min(Date) in line graph is not working in Qliksense

batch_meta_data_id = 96, opens on 3rd hour = 85. opens on 4th hour = 109. so like this totally 1048 opens on first day(i.e 10/31/2017).

Re: Count of Min(Date) in line graph is not working in Qliksense

I can see for 96, we have min formatted date is 10-31-2017..

which is OK..

but as per first_email_send_date the time is all 3am..

so how come 4th hour is coming here?

Community Browser