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

[spsrk_84]

Hi Supriya,

I have got the solution in a different way, Please see the attached file.

Thanks,

Shivaram

[berryandcherry6]

Hi Sunny,

Thanks for reply, it worked for me.

When you have time or if you have time, can you explain this equation

i did some homework about firstsortedvalu, i found

FirstSortedValue ([ distinct ] value, sort-weight [, rank ])

In document, i do was able to understand simple example given there, but i am not getting how you figured out this equation, especially why you using  batch_meta_data_id={45}, campaign_end_date = {"$(='>' & Date(Today(),'MM/DD/YYYY'))"}>}  before value.

Your explaination will be very helpful to all.

[berryandcherry6]

Hey shivaram,

I am using qliksense, so can you post expression what you are using.

Regards,
Supriya

[spsrk_84]

Hi Supriya,

below is the data set

LOAD * INLINE [

    Date, Sales

    10/13/2016, 20   

    10/14/2016, 20   

    10/14/2016, 20

    10/14/2016, 20

    10/15/2016, 20  

    10/16/2016, 20 

    10/17/2016, 10

    10/17/2016, 10

    10/17/2016, 10

    10/18/2016, 5

    11/10/2016, 20

];

I created two variables

1) which captures today's date i.e. vToday =TOday()

2) Created another variable which pick max date apart from todays date

i.e vMAXDate=DATE(MAX({<Date={"<$(vToday)"}>}Date))

And Final expression in the chart i have written is  =SUM({<Date={"$(vMaxDate)"}>}Sales)

Pls check

[sunny_talwar]

Not sure where you picked the above syntax from, but FirstSortedValue() function allows for set expression also...

FirstSortedValue([{SetExpression}] [DISTINCT] [TOTAL [<fld {,fld}>]] value, sort_weight [,rank])

Check here: https://help.qlik.com/en-US/sense/3.1/Subsystems/Hub/Content/ChartFunctions/BasicAggregationFunction...

So basically what you are asking is just a set modifier.

Does that help?