Announcements
cancel
Showing results for
Did you mean:
Creator III

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)

14 Replies
Creator III

Hi Supriya,

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

Thanks,

Shivaram

Creator III
Author

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.

Creator III
Author

Hey shivaram,

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

Regards,
Supriya

Creator III

Hi Supriya,

below is the data set

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

MVP

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

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

Does that help?