Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
i have Active campaigns, where expiration date is greater than today(). In this i need to build a line graph with first latest, second latest and third latest record.
Below is the script
LOAD `campaign_id`,
`batch_id` ,
`start_date` ,
date(floor(expiration_date),'MM-DD-YYYY') as expiration_date,
`campaign_name`;
SQL SELECT `campaign_id`,
`start_date`,
expiration_date,
`batch_meta_data_id`,
`campaign_name`
FROM CAMPAIGN;
In front end
sum({$< client_id={'15'},batch_id={'30'},expiration_date = {"$('>' & Date(Today(),'MM-DD-YYYY'))"}>}opened)
Now i need to get sum of opened for first latest, second latest and third latest record specifically. How to get those,
Any kind of help or suggestion is appreciated.
Is this expression working for you?
Sum({$< client_id={'15'},batch_id={'30'},expiration_date = {"$('>' & Date(Today(),'MM-DD-YYYY'))"}>}opened)
Related to your question.... if you have these dates
03/13/2017
03/14/2017
03/15/2017
03/16/2017
03/17/2017
03/18/2017
1st latest would be 03/14/2017
2nd would be 03/15/2017 and so on??
Is this expression working for you?
Sum({$< client_id={'15'},batch_id={'30'},expiration_date = {"$('>' & Date(Today(),'MM-DD-YYYY'))"}>}opened)
No, this is not working
Yes, you are right.
Start Date Expiration Date campaign_id
01/01/2017 01/20/2017 23
01/05/2017 01/12/2017 24
02/02/2017 02/20/2017 26
03/13/2017 03/26/2017 27
03/20/2017 03/26/2017 28
03/06/2017 03/19/2017 29
02/20/2017 02/26/2017 30
Here
first latest - 03/06/2017 03/19/2017
Second latest - 03/13/2017 03/26/2017
third lates - 03/20/2017 03/26/2017 ......
So this is now based on the start date or the combination or start and expiration date?
Its both of Expiration Date and Start Date
to check whether campaigns active or not i take Expiration Date >= today()
to check latest i take start_date with Expiration Date >= today()
Hi Sunny,
Do you need more information on this?
I do actually, I am still trying to figure out where exactly are you going to be showing this? In a text box object or a table? If it is in a table, what all are you dimensions here?
Hi,
i need to display in textbox. Dont think about line graph as i mentioned in question.
If i need to display in text box, how should be my query?
So you want three text boxes? and what is opened here??? I mean what exactly those text boxes display? Dates or another value...
Here opened are number of opened emails by users.
textbox 1 - display sum of Opened for first latest campaign
textbox 2 - display sum of Opened for second latest campaign
textbox 3 - display sum of Opened for third latest campaign