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

[berryandcherry6]

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.

[berryandcherry6]

I am sorry, dimension is Hour(first_opened_date).

[MK_QSL]

Dimension

=hour(first_opened_date)

Expression

COUNT(Distinct Aggr(IF(formatted_send_date = Min(Total <batch_meta_data_id>formatted_send_date),invitation_id),formatted_send_date,invitation_id,batch_meta_data_id))

[berryandcherry6]

Hi Manish.

I am not getting proper counts,

For  batch_meta_data_id = 96, opens on 3rd hour = 85. opens on 4th hour = 109..

but it showing 102 for 3rd hour , 135 for 4th hour.

And where should i add open={'1'}

[MK_QSL]

102 is the correct answer..

for formatted send date 10-31-2017

you have 102 invitation id

[berryandcherry6]

Hi Manish,

I need invitations send and opened on the same first day(i.e 10-31-2017) with opened = 1. But now its showing all invitations count sent on first day regardless whether its opened on same first day or not. so you are getting 102.

[MK_QSL]

little busy right now.. so will look in to this today evening..

[MK_QSL · Accepted solution]

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

[berryandcherry6]

Yes, you are right! And your solution works.

Now i want to show above counts in percentage by delivered. For this i divided above Opens count by Delivered counts. Delivered counts are also same counts, which are delivered on first day for batch hourly.

For this i modified your code to this

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

    Aggr(

        IF(

            formatted_send_date = Min(Total <batch_meta_data_id>formatted_send_date)

                and

            Floor(first_delivered_date) = formatted_send_date

            ,invitation_id)

        ,formatted_send_date,invitation_id,batch_meta_data_id)

)

)

What should be my dimension. As i am taking both delivered and opens into account?

But this shows all wrong values.

[MK_QSL]

What is delivered count? Those which are not opened = 1?

What is the output in number you are looking for?

I will try it for you.