Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a camapign table with
campaign_id, batch_meta_data_id, campaign_name, start_date, expiration_date
2 43 camp1 10/07/2016 10/10/2016
4 43 camp2 10/08/2016 17/10/2016
5 44 camp3 20/10/2016 10/07/2016
6 43 camp4 10/07/2016 10/13/2016
offer table
offer_id, client_id, batch_meta_data_id campaign_id
2 3 43 2
3 4 43 4
4 4 44 5
5 4 43 6
log table
offer id log_type
2 33
2 34
3 33
4 33
5 33
2 33
What i need :
i need to get distinct count of offer id where expiration_date is nearest to today date(top 1) and less than today and log_type = 33 and batch_meta_data_id = 43.
In sql query i get by this:
SELECT count(Distinct(offer_id)) FROM LOG where offer_id IN (select offer_id from offer where campaign_id IN (SELECT campaign_id FROM CAMPAIGN where expiration_date < NOW() and batch_meta_data_id = 43 order by expiration_date) and log_type = 33
How could i achieve this in Qliksense using set analysis. Please help me on this.
Try with:
count({<log_type = {'33'},batch_meta_data_id = {'43'},expiration_date = {">=today()"}>}offer_id)
Hi Shraddha,
sorry, i have edited my query, its less than
count({<log_type = {'33'},batch_meta_data_id = {'43'},expiration_date = {"<=today()"}>}offer_id)
here it gives me offer_id count where for all expiration_date is less than today or today, But i need top one, which is nearest to today that is top 1.
Try
count({<log_type = {'33'},batch_meta_data_id = {'43'},expiration_date = {"$(=max(expiration_date))"}>}offer_id)
Hi Supriya,
Can you please post your expecting output?
Thanks,
We Can do this on Edit Script.
Please find the below attached .qvf file.
Hope this will helps you.
Thanks,
Sorry
This is Updated Qvf
Thanks,
Something similar to the other post won't work here?
=FirstSortedValue({$<Batch_meta_data_id={'43'}, log_type = {'33'}, campaign_end_date = {"$(='<' & Date(Today()))"}>} Aggr(Sum({<Batch_meta_data_id={'43'}, log_type = {'33'}>} sign_up), campaign_end_date), -Aggr(campaign_end_date, campaign_end_date))