Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
berryandcherry6
Not applicable

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
kamal_sanguri
Not applicable

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

sunny_talwar
Not applicable

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)

swuehl
Not applicable

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?

swuehl
Not applicable

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)

berryandcherry6
Not applicable

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.

berryandcherry6
Not applicable

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.

swuehl
Not applicable

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

berryandcherry6
Not applicable

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.

berryandcherry6
Not applicable

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.