Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
berryandcherry6
Contributor II

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

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

Tags (1)
1 Solution

Accepted Solutions

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

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

Capture.PNG

14 Replies

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

May be this:

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

berryandcherry6
Contributor II

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

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.

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

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
Contributor II

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

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.

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

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

Capture.PNG

berryandcherry6
Contributor II

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

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?

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

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

Capture.PNG

berryandcherry6
Contributor II

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

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

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

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'


Capture.PNG