Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Contributor II

Getting wrong counts in setAnalysis

Hi all,

I am trying to get counts where final_test_status={'passed'} and campaign_id are from batch 23 and 24 and its current_start_date = {"$(=Date(WeekStart(today()) + 1, 'MM-DD-YYYY'))"}>}.

I got required campaign_id from:

Concat(DISTINCT {<campaign_id = p({<batch_meta_data_id = {23, 24}, current_start_date = {"$(=Date(WeekStart(today()) + 1, 'MM-DD-YYYY'))"}>})

>} campaign_id, ', ') = 350,351,352,353

Putting this expresssion inside set analysis to get counts gives wrong count:

Count({$<final_test_status={'passed'}, campaign_id = p({<batch_meta_data_id = {23, 24}, current_start_date = {"$(=Date(WeekStart(Today()) + 1, 'MM-DD-YYYY'))"}>})>} course_completed)

To debug hardcoding campaign_id values in setAnalysis gives correct count :

Count({$< final_test_status={'passed'},campaign_id ={'350','351','352','353'}>} course_completed)


Why values are getting differed.Please help me on this regard.



Thanks.

Supriya

9 Replies
Highlighted
Valued Contributor

Re: Getting wrong counts in setAnalysis

Please check if you have any filter applied and also check the alternate state of the object where you have this expression.

-Kamal

Highlighted

Re: Getting wrong counts in setAnalysis

How about doing it this way:

Count({$<final_test_status={'passed'}, campaignid = {$(=Concat(DISTINCT {<campaign_id = p({<batch_meta_data_id = {23, 24}, current_start_date = {"$(=Date(WeekStart(today()) + 1, 'MM-DD-YYYY'))"}>})>} campaign_id, ', ')}>} course_completed)

Highlighted
MVP
MVP

Re: Getting wrong counts in setAnalysis

Where do you test for 'passed' status here:

Concat(DISTINCT {<campaign_id = p({<batch_meta_data_id = {23, 24}, current_start_date = {"$(=Date(WeekStart(today()) + 1, 'MM-DD-YYYY'))"}>})

>} campaign_id, ', ') = 350,351,352,353

?

If above returns the correct numbers, wouldn't then

=Count(DISTINCT {<campaign_id = p({<batch_meta_data_id = {23, 24}, current_start_date = {"$(=Date(WeekStart(today()) + 1, 'MM-DD-YYYY'))"}>})

>} campaign_id)

return the required 4?

Highlighted
MVP
MVP

Re: Getting wrong counts in setAnalysis

Also note that you used campaignid instead of campaign_id here:

Count({$<final_test_status={'passed'}, campaignid = p({<batch_meta_data_id = {23, 24}, current_start_date = {"$(=Date(WeekStart(Today()) + 1, 'MM-DD-YYYY'))"}>})>} course_completed)

Highlighted
Contributor II

Re: Getting wrong counts in setAnalysis

HI swuehl,

Thanks for reply

campaignid is a typo error,it is campaign_id.

Here i want count of datas where its final_test_status={'passed'} and for only those campaign_id. Not the count of campaign_id.

Highlighted
Contributor II

Re: Getting wrong counts in setAnalysis

Hi Sunny,

Count({$<final_test_status={'passed'}, campaign_id = {$(=Concat(DISTINCT {<campaign_id = p({<batch_meta_data_id = {23, 24},

current_start_date = {"$(=Date(WeekStart(today()) + 1, 'MM-DD-YYYY'))"}>})>} campaign_id, ', '))}    >} course_completed)

gives me zero count.

Highlighted
MVP
MVP

Re: Getting wrong counts in setAnalysis

campaignid is a typo error,it is campaign_id.

Here i want count of datas where its final_test_status={'passed'} and for only those campaign_id. Not the count of campaign_id.

Ok, I just wonder if you are not comparing different things looking at your two different expressions, hence you can't compare the results.

If you want to get help to get your correct count, I think we would need to know more about your data model and data.

It would would be best if you could upload a small sample QVW

Highlighted
Contributor II

Re: Getting wrong counts in setAnalysis

Hi  swuehl ,

Please look into attached qvf file, i need to get counts by putting condition on front end instead of scripting condition in loader.

LOAD `campaign_id` as  campaignid,

`batch_meta_data_id`, 

    date(floor(start_date),'MM-DD-YYYY') as current_start_date,

    `expiration_date`;

SQL SELECT

`batch_meta_data_id`,

`campaign_id`,

`start_date`,

`expiration_date`

FROM CAMPAIGN;

CurrentCampaignData:

Load

batch_meta_data_id,

campaignid as current_campaign_id

Resident CAMPAIGN

WHERE ( current_start_date = Date(weekstart (Num ( today() ),-1 ) +1)) and match(batch_meta_data_id ,'23','24');

As if now when i have scripted condition in loader  as above and put


count({$<DateType= {'completion'},final_test_status={'passed'},campaign_id = P(current_campaign_id)>} course_completed)

in front end i am getting correct count ,But  When instead of scripting in loader i put whole condition in front end i am not getting proper count value.

Please help me on this.

Highlighted
Contributor II

Re: Getting wrong counts in setAnalysis

Hi sunny,

Please look into attached sample,this gives me correct count only when condition in put in loader ,But when i put this condition in front end it wont give correct correct count value.

59 count is the correct count

Please help me on this.