Passing dynamic values to set analysis

[#1]

Hi all,

I have table where campaign_id is a field which has many values, each week campaigns are created on client and batch specific.

For ex: batch_id = 1 has 4 campaigns and batch_id = 2 has 3 campaigns every week.

I need sum of user completed on batch specific. for ex:

Sum({$<batch_id = {'1'}, campaign_id = {"Need to pass dynamic values to here depending on week"}>}user_completed)

If i want current week campaigns i need to pass the current week campaigns, If last week i need to pass last week campaigns.

campaign_start_date    batch_id      campaign_id

     05-17-2016                 1                  400

    05-17-2016                 1                  401

    05-17-2016                 1                  402

    05-17-2016                 1                  403

Now i need to include these 4 campaign_id's to above set analysis how can i achieve this.

Regards,

Pramod

Message was edited by: Kumar Pramod

[sunny_talwar]

Would it be possible to share a sample to look at this?

[#3]

sorry sunny, working on server not having any sample data to share.

above example table looks same as my table in db

[Josh_Good]

Try

Sum({$<batch_id = {'1'}, campaign_id = { $(=chr(39) & Concat(Distinct campaign_id, ',') & chr(39)) }>}user_completed)

The expression:

=chr(39) & Concat(Distinct campaign_id, ',') & chr(39)

will return all CampaignIDs included in your selection so if you select 05-17-2016 it will return:

'400,401,402,403'

based on your data above.

I hope this helps.

-Josh

Qlik

[#5]

Thanks for the reply Josh good,

I am using a Expression like below,

count({$<DateType= {'completion'},final_test_status={'passed'},

campaign_id = $(=chr(39) & Concat(Distinct current_campaign_id, ',') & chr(39))>} invitation_id

This is not filtering on the basis of campaign_id it's selecting all the invitation_id where DateType= {'completion'},final_test_status={'passed'}.

=chr(39) & Concat(Distinct campaign_id, ',') & chr(39)  this is returning value when i print it in text and image object but in the expression

[#6]

Please find the sample.qvf file.

Need count of completed on the basis of batch and campaign_id.

Help on it.

[#7]

Hi Josh Good,

stalwar1

If i hard code campaign_id's i am getting the accurate answer but not able to pass the id's dynamically.

count({$<DateType= {'completion'},final_test_status={'passed'},

campaign_id ={'400','401','402','403'}>} invitation_id

below one is

not working,

count({$<DateType= {'completion'},final_test_status={'passed'},

campaign_id = $(=chr(39) & Concat(Distinct current_campaign_id, ',') & chr(39))>} invitation_id

I have a table like below,

campaign_start_date    batch_id      campaign_id

      05-17-2016                2               406

    05-17-2016                  2                405

    05-17-2016                 2                  404

     05-17-2016                 1                  400

    05-17-2016                 1                  401

    05-17-2016                 1                  402

    05-17-2016                 1                  403

     05-12-2016                1                   359

     05-12-2016                1                   358

     05-12-2016                1                   357

     05-12-2016                2                  356

     05-12-2016                2                  355

from this table i need to pass a campaign_id of only batch_id = 1 and the date = 05-17-2016 to the above Set analysis.

Please help on this

[Kushal_Chawda]

Create the Variable on front end vDynamicCampaign and put below expression

= concat( distinct chr(39) & campaign_id  & chr(39) ,',')

use below expression

count({$<DateType= {"completion"},final_test_status={"passed"},

campaign_id = {"$(=concat( distinct chr(39) & campaign_id  & chr(39) ,','))"}>} invitation_id)

[#9]

How it will select distinct campaign_id for batch_id = 1, because we have not mentioned any batch field in above expression

[Kushal_Chawda]

Either you do selection on Batch ID or you need to hardcode the batch ID.