Display sum of top N record which is less than today date in qlik.

[berryandcherry6]

Hi,

i am trying to get sum of sign_up, where it finds max of  campaign_end_date, which is less than today's date

SQL query would be:

"SELECT sum(sign_up) AS Signupcount FROM CAMPAIGNMETRICS "

  + " where  campaign_metrics_id = (SELECT campaign_id  FROM CAMPAIGN  where campaign_end_date < NOW() and  batch_meta_data_id = '45'  order by campaign_end_date DESC LIMIT 1)";

I am trying to execute below code in qlik, but not getting any value

Sum({$<batch_meta_data_id={'45'}, campaign_end_date = p({"$(=Date(Max({1}campaign_end_date, 1), 'MM-DD-YYYY'))"})*p({campaign_end_date={"<$(=Date(today()))"}} campaign_end_date)>} sign_up)

How could i achieve my requirement? Please help me on this

[sunny_talwar]

May be this:

Sum({$<batch_meta_data_id={'45'}, campaign_end_date = {"<$(=Date(Today()'MM-DD-YYYY'))"}>} sign_up)

[berryandcherry6]

Hi sunny,

This will give all sum of sign_up, where campaign_end_date is less than today.

But i need the greatest  one campaign_end_date that is less than today.

[sunny_talwar]

Try with FirstSortedValue:

FirstSortedValue({$<batch_meta_data_id={'45'}, campaign_end_date = {"<$(=Date(Today()'MM-DD-YYYY'))"}>} Aggr(Sum(sign_up), campaign_end_date), -campaign_end_date)

[berryandcherry6]

Hi sunny,

For example

campaign_end_date   sign_up   Batch_meta_data_id

   2016/10/12                     2                             45

   2016/10/13                    2                             45

   2016/10/13                    2                             45

   2016/10/13                    2                             45

   2016/10/14                     1                            45

   2016/10/15                     2                             45

   2016/10/16                    2                             45

   2016/10/13                    2                             45

   2016/10/16                    2                             45

Here i should choose,  2016/10/16 as campaign_end_date   and get sum of that as 4.

I mean campaign_end_date  which is nearest to today date and less than today.

From above given expresion it gives me wrong count.

[sunny_talwar]

Try this:

=FirstSortedValue({$<Batch_meta_data_id={'45'}, campaign_end_date = {"$(='<' & Date(Today()))"}>} Aggr(Sum(sign_up), campaign_end_date), -Aggr(campaign_end_date, campaign_end_date))

[berryandcherry6]

Hi sunny,

I have attached sample qvf, in that it showing sum of signup of all campaignes of that batch (45). showing 846

And if you filter data for batch = 45 , then it shows correct value of 246.

Could you please check this?

[sunny_talwar · Accepted solution]

Check this:

FirstSortedValue({$<batch_meta_data_id={45}, campaign_end_date = {"$(='<' & Date(Today(),'MM-DD-YYYY'))"}>}

Aggr(Sum({$<batch_meta_data_id={45}>}sign_up), campaign_end_date), -Aggr(campaign_end_date, campaign_end_date))

[berryandcherry6]

Thanks, it worked

i am now trying to get campaign_end_date greater than today , for this i have changed angle operator sign, but it gives me nothing, I have data for that but not showing any count

[sunny_talwar]

Not sure why, but this new sample you have provided have a different date format. Try this

FirstSortedValue({$<batch_meta_data_id={45}, campaign_end_date = {"$(='>' & Date(Today(),'MM/DD/YYYY'))"}>}

Aggr(Sum({$<batch_meta_data_id={45}>}sign_up), campaign_end_date), Aggr(campaign_end_date, campaign_end_date))

Changed the date format from 'MM-DD-YYYY' to 'MM/DD/YYYY'