14 Replies Latest reply: Nov 10, 2016 5:11 AM by Sunny Talwar RSS

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

    Supriya R



      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