topic Re: Prblem in Set Analysis logic in QlikView

Prblem in Set Analysis logic

Anonymous — Tue, 12 May 2015 06:34:33 GMT

Hi,

I am new to Qlikview and facing strange issue with small set analysis expression

This is my expression

SUM({<[Campaign Channel]={'PPC'}>}[Site Visit Vis Num])

Actually there is data for campaign channel and as well for Site Visit Vis Num as I checked individually.

If I combine as expression it is returning '0' for all dates.

Can anybody help me how to resolve this issue. Thanks in advance

Regards,

Kumar

Re: Prblem in Set Analysis logic

maleksafa — Tue, 12 May 2015 06:36:44 GMT

if you change the expression to SUM([Site Visit Vis Num]) and add a listbox for Campaign Channel, then select PPC what will you get?

Re: Prblem in Set Analysis logic

Anonymous — Tue, 12 May 2015 06:45:18 GMT

Hi Malek

This is what I am getting.

Regards,

Kumar.

Re: Prblem in Set Analysis logic

maleksafa — Tue, 12 May 2015 06:49:57 GMT

i can't see anything wrong about the expression then, just double check if the dimension name is correct.

can you upload a sample document, i can help you more.

Re: Prblem in Set Analysis logic

Anonymous — Tue, 12 May 2015 06:52:27 GMT

This QVW and QVD file sizes are big size .

Regards,

Kumar

Re: Prblem in Set Analysis logic

Tue, 12 May 2015 06:57:43 GMT

Can you TRIM the ChannelCount in the script, I guess PPC is having leading spaces.

Also cross check the field name, I can see Channel Count and Campaign Channel fields.

Re: Prblem in Set Analysis logic

Anonymous — Tue, 12 May 2015 07:18:15 GMT

Hi Angad,

I have trimmed it still it is having zero's. Campaign channel and Channel count both are same.

For all campaign channels it is having same problem.

Re: Prblem in Set Analysis logic

Tue, 12 May 2015 07:24:09 GMT

The set analysis expression looks good.

Can you try IF statement for testing?

sum(if(Campaign Channel = 'PPC',[Site Visit Vis Num]))

if(Campaign Channel = 'PPC',sum([Site Visit Vis Num]))

Re: Prblem in Set Analysis logic

Anonymous — Tue, 12 May 2015 07:33:35 GMT

Can you find the screen shot after modifying the expression

Re: Prblem in Set Analysis logic

Tue, 12 May 2015 07:39:30 GMT

Reddy, I did not the {} brackets around PPC. Please remove those and test again.

Re: Prblem in Set Analysis logic

Anonymous — Tue, 12 May 2015 07:46:16 GMT

Hi Angad,

Sorry, Pls find the screen shot

Re: Prblem in Set Analysis logic

Tue, 12 May 2015 07:54:54 GMT

There has to be a minor thing, which we are missing in here.

Can you please share your QVW ?

btw, how come I see numbers in the Display Visits column adjacent to PPC??

Re: Prblem in Set Analysis logic

Anonymous — Tue, 12 May 2015 08:26:09 GMT

In that expression I removed Campaign channel = 'Display'. This QVW file is size is 170 Mb

Regards,

Kumar

Re: Prblem in Set Analysis logic

Tue, 12 May 2015 08:58:33 GMT

Can you create a new QVW file where you have only that chart?

Re: Prblem in Set Analysis logic

oleg_datsenko — Tue, 12 May 2015 10:23:26 GMT

Hi Kumar,

Try to use SUM function above IF function, like this:

=sum(

if(your_condition,[Site Visit Vis Num],0)

)

It was a bit ...strange for me too for a first time... after Excel

And about the script... please, open a script(CTRL+E), open Search window(CTRL+F), find "as [Site Visit Vis Num]" and provide us with the load block(s) which contains this string.

I suppose it was wrong interpreted(not like a number).

Also, try to define a set identifier in your set expression:

SUM({1<[Campaign Channel]={'PPC'}>}[Site Visit Vis Num]) - if you want to ignore all current selections

SUM({$<[Campaign Channel]={'PPC'}>}[Site Visit Vis Num]) - if you want to consider current selections

It looks like you've missed a set identifier... but I'm not sure that it really matters.

Oleg

Re: Prblem in Set Analysis logic

Anonymous — Tue, 12 May 2015 10:42:45 GMT

I tried with that expression, but still returns 0

pls find the fact set block with that field as highlighted

Fact_site_visit:

LOAD site_visit_key,

site_visit_visitor_key,

site_visit_visitor_ip_key,

site_visit_team_key,

site_visit_campaign_key,

//site_visit_account_key,

site_visit_start_datetime_key,

site_visit_start_hms_time_key,

site_visit_visitor_system_configuration_key,

site_visit_search_key,

site_visit_page_key,

site_visit_geography_key,

site_visit_parameter_key,

site_visit_referrer_parameter_key,

site_visit_referring_url_page_key,

site_visit_end_datetime_key,

site_visit_end_hms_time_key,

site_visit_racksid as [Site Visit RackSID],

site_visit_record_count as [Site Visit Record Count],

site_visit_sys_generated_visit_id,

site_visit_vis_num as [Site Visit Vis Num],

site_visit_page_visit_num as [Site Visit Page Num],

site_visit_post_page_event as [Site Visit Post Page Event],

SubField(site_visit_post_page_event,',') as Post_Page_Event,

site_visit_hit_source as [Site Visit Hit Source],

site_visit_page_duration as [Site Visit Page Duration],

site_visit_chat_started_flag as [SIte Visit Chat Started Flag],

site_visit_email_and_apps_signup_flag as [SiteVisit Email and Apps Signup Flag],

site_visit_cloud_signups_flag as [SiteVisit Cloud Signups Flag],

site_visit_site_submissions_flag as [SiteVisit Site Submissions Flag],

site_visit_ordered_flag as [SiteVisit Orders Flag],

site_visit_source_system_name as [Site Visit Source System Name],

site_visit_source_system_column as [Site Visit Source System Column],

site_visit_source_system_id_nk as [Site Visit Source System ID nk]

FROM

[fact_site_visit.qvd]

(qvd);

Re: Prblem in Set Analysis logic

Tue, 12 May 2015 12:00:01 GMT

Can you please try

SUM({$<[Campaign Channel]={'PPC'}>} if(isnull([Site Visit Vis Num])=-1,0,[Site Visit Vis Num]))

you could also be getting the missing values first. May be you should double click on the field to sort in order to check if the column holds any values at all.

Re: Prblem in Set Analysis logic

oleg_datsenko — Tue, 12 May 2015 12:25:09 GMT

Kumar,

and now, could you please open the Table viewer(CTRL+T), find a tab "Fact_site_visit" and make a screenshot with the field "site_visit_vis_num" ?

Are there no more any [Site Visit Vis Num] fields in your script? Is it unique indeed?

Oleg

Re: Prblem in Set Analysis logic

Anonymous — Tue, 12 May 2015 15:34:15 GMT

Hi,

Can you pls find the screen shot.

Regards,

Kumar

Re: Prblem in Set Analysis logic

Anonymous — Tue, 12 May 2015 15:49:53 GMT

Hi,

Still I am seeing 0's only and having data also.

data is available in both the tables also