Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
berryandcherry6
Creator II
Creator II

Accumulation in line graph not happening even after using rangesum(above)

Hi,

i have created a line graph where it displays counts of open, which is delivered and opened on firstday for a particular batch hourly.

Dimension:Hour(formatted_opened_date)

Expression:COUNT(Distinct Aggr(IF(formatted_opened_date = Min(Total <batch_meta_data_id>formatted_opened_date),invitation_id),formatted_opened_date,invitation_id,batch_meta_data_id))

Now i want to do a accumulation line chart, So i changed expression to :

rangesum(above(COUNT(Distinct Aggr(IF(formatted_opened_date = Min(Total <batch_meta_data_id>formatted_opened_date),invitation_id),formatted_opened_date,invitation_id,batch_meta_data_id)), 0, Rowno()))

But this is not at all giving accumulated records.

Please help me on this!. I have attached a sample app.

1 Solution

Accepted Solutions
sunny_talwar

You just like fooling around with us, don't you...

Capture.PNG

Aggr(rangesum(above(COUNT(Distinct Aggr(IF(formatted_opened_date = Min(Total <batch_meta_data_id>formatted_opened_date),invitation_id),hour_first_opened_date,invitation_id,batch_meta_data_id)), 0, Rowno())), batch_meta_data_id, (hour_first_opened_date, (Numeric)))

I didn't ask you to change these guys to the new hour field

View solution in original post

15 Replies
sunny_talwar

Would you be able to create this in the script?

Hour(first_opened_date) as hour_first_opened_date

and then try this

Aggr(rangesum(above(COUNT(Distinct Aggr(IF(formatted_opened_date = Min(Total <batch_meta_data_id>formatted_opened_date),invitation_id),formatted_opened_date,invitation_id,batch_meta_data_id)), 0, Rowno())), batch_meta_data_id, (hour_first_opened_date, (NUMERIC)))

berryandcherry6
Creator II
Creator II
Author

Hi Sunny,

something not right,

i changed script like below:

LOAD `invitation_id`,

    `client_id`,

    `batch_meta_data_id`,

    first_email_send_date,

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

    delivered,

    `first_delivered_date`,

    opened,

    `first_opened_date`,

     Hour(first_email_send_date) as hour_first_opened_date,

     date(floor(first_opened_date),'MM-DD-YYYY') as formatted_opened_date;

then followed ur expression. By the way NUMERIC there was no keyword like this in qliksense. so it prompt to change to NumericCount.


The output looks like below


Capture.PNG

sunny_talwar

Can you attach this new qvf file?

berryandcherry6
Creator II
Creator II
Author

Please see attached file

sunny_talwar

I think you calculated the Hour for incorrect field

Capture.PNG

You needed to calculate the Hour for first_opened_date

berryandcherry6
Creator II
Creator II
Author

I am sorry, i changed and checked graph shows like below

Capture.PNG

sunny_talwar

You just like fooling around with us, don't you...

Capture.PNG

Aggr(rangesum(above(COUNT(Distinct Aggr(IF(formatted_opened_date = Min(Total <batch_meta_data_id>formatted_opened_date),invitation_id),hour_first_opened_date,invitation_id,batch_meta_data_id)), 0, Rowno())), batch_meta_data_id, (hour_first_opened_date, (Numeric)))

I didn't ask you to change these guys to the new hour field

berryandcherry6
Creator II
Creator II
Author

Sorry Sunny, but i didn't mean it. I make sure i wont repeat this again and be alert.

Thanks! Now i  am getting correct values.

Going one more step forward, for percentage i need to do like this  (opens on firstday/ sents on firstday) .

Is this possible? How could i do this. I like to give a try on this.

Thanks

Supriya

sunny_talwar

Not sure I understand, I think you will have to elaborate?