Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
berryandcherry6
Creator III
Creator III

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
Specialist
Specialist

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

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
MVP
MVP

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
MVP
MVP

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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
MVP
MVP

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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.