select first and second latest record

[berryandcherry6]

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.

[sunny_talwar]

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??

[berryandcherry6]

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

[sunny_talwar]

So this is now based on the start date or the combination or start and expiration date?

[berryandcherry6]

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

[berryandcherry6]

Hi Sunny,

Do you need more information on this?

[sunny_talwar]

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?

[berryandcherry6]

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?

[sunny_talwar]

So you want three text boxes? and what is opened here??? I mean what exactly those text boxes display? Dates or another value...

[berryandcherry6]

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