Advanced Analytics Job Touches

[davidrb84]

Hi,

I'm looking to reproduce something I can do simply in database direct as a measure.

Namely a total number of jobs 'touched' by a given analyst each day.

The SQL I've got that works is 

select count(*), h_type, h_content from h_buz_activities 
where SUBSTR(h_actor,14) = 'BloggsJ'
and date(h_published) = '2019-07-15'
group by h_target

This gives all the jobs touched by BloggsJ on the 15th July, I've grouped by h_target to mean you only get credit once per job.

I'm having trouble reproducing this as a measure. I currently have the below, but it gives me all 0s.

 

 

[Guest Chaz]

@davidrb84 the 'Query where clause' field should just contain everything after 'WHERE' in your query... so in your example you just need to put in:

SUBSTR(h_actor,14) = 'BloggsJ'
and date(h_published) = '2019-07-15'
group by h_target