Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Passing dynamic values to set analysis

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

Tags (1)
17 Replies
MVP
MVP

Re: Passing dynamic values to set analysis

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

Not applicable

Re: Passing dynamic values to set analysis

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

above example table looks same as my table in db

Employee
Employee

Re: Passing dynamic values to set analysis

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


Not applicable

Re: Passing dynamic values to set analysis

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

Not applicable

Re: Passing dynamic values to set analysis

Please find the sample.qvf file.

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

Help on it.

Not applicable

Re: Passing dynamic values to set analysis

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

    

Re: Passing dynamic values to set analysis

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)

Not applicable

Re: Passing dynamic values to set analysis

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

Re: Passing dynamic values to set analysis

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

Community Browser