Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

berryandcherry6
Contributor II

expression to get sum for different campaign/record

Hi,

i have a table like below

campaign_id clientId  batchMetaDataId       campaignName         startDate     expirationDate

23                       67                78                  campaignName1       8/15/2017       8/27/2017

24                       67                78                 campaignName2        8/8/2017         8/20/2017

29                      67                78                 campaignName8       8/1/2017          8/13/2017

24                      67                 78                 campaignName3        8/15/2017        8/27/2017

26                     67                   91                 campaignName4        8/8/2017         8/20/2017

27                       67                  92                 campaignName5       8/15/2017        8/27/2017

28                       67                   9 1                 campaignName6      8/8/2017         8/20/2017

30                      67                   9 1              campaignName9       7/25/2017        8/6/2017

To calculate sum of sent counts for all active campaigns by using below code

Sum({$<batch_meta_data_id={'78','91'},expiration_date = {"$(='>=' & Date(Today(),'MM-DD-YYYY'))"},comm_type_id={'44'}>}sent)

But now i want to find sum of counts for each active campaigns separately, how could i do this? let me know if anyone need more info on this.


Tags (1)
5 Replies
roharoha
Valued Contributor III

Re: expression to get sum for different campaign/record

just add a dimension "campaign_id" and leave your expression as it is, if it works

sureshqv
Esteemed Contributor III

Re: expression to get sum for different campaign/record

try this

aggr(Sum({$<batch_meta_data_id={'78','91'},expiration_date = {"$(='>=' & Date(Today(),'MM-DD-YYYY'))"},comm_type_id={'44'}>}sent),campaign_id)

sureshqv
Esteemed Contributor III

Re: expression to get sum for different campaign/record

may be this?aggr.PNG

berryandcherry6
Contributor II

Re: expression to get sum for different campaign/record

i want to display sum of counts for particular campaigns in a custom straight table. So i can't use campaign_id in straght table. I want to do it in expression. By using controls like max(expirationDate, 2)  or firstsortedvalue. but i dont know how to modify my code to use this controls to get right reult

berryandcherry6
Contributor II

Re: expression to get sum for different campaign/record

Hi chanty,

Thanks for reply,  here i need to get data for only active campaigns

i have a table like below

dimension    active campaign 1  active campaign 2

sent                 23                                5

delivered         8                                  6

To get active campaign, i take expiration_date > now(),  so for client_id - 67, batch_id = 78, i have 2 active campaigns 23 and 24 campaign_id which are active. so for each these active campaign i need to get counts