Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
berryandcherry6
Creator III
Creator III

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
MK_QSL
MVP
MVP

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..!!

View solution in original post

18 Replies
MK_QSL
MVP
MVP

What is the dimension you are using?

MK_QSL
MVP
MVP

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
Creator III
Creator III
Author

Dimension: Hour(first_opened_date)

berryandcherry6
Creator III
Creator III
Author

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.

MK_QSL
MVP
MVP

What is the output you are looking for?

berryandcherry6
Creator III
Creator III
Author

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

MK_QSL
MVP
MVP

I am talking about output.. Not description..

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

berryandcherry6
Creator III
Creator III
Author

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).

MK_QSL
MVP
MVP

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?