Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Chewitt
Contributor II
Contributor II

Date1 > Date 2 Set analysis

Hi, 

I want to compare the number of crimes reported before and after new police patrols are implemented.

The number of crimes data has a daily timestamp (Date1) and is a continuous feed of data. A different date for each postcode.

The data feed documenting the implementation of the patrols has a single data (Date2) against each postcode.

I would like to know the average crime rate in the postcode before and after the differnet timestamps for each postcode.

So far i have tried;

avg({$<[Date1]={"=<[Date2]"}>}[Crime Rate]) to get the average crime rate before.

However, i just get nulls returned.

When i have tinkered, i have tried swapping out each date dimension for a single date, and it has returned data, so i know they are being interpreted in a date format.

Labels (4)
3 Replies
sunny_talwar

May be try this

Avg(If([Date1] <= [Date2], [Crime Rate]))

or perform this in the script like this

If([Date1] <= [Date2], 1, 0) as Flag

and then this

Avg({<Flag = {'1'}>} [Crime Rate])

 

Chewitt
Contributor II
Contributor II
Author

Thanks Sunny.

Next, I'm wanting to add a KPI which counts the number of postcodes where the average crime rate has improved after the addition of police patrols.

Likewise, i would also like to count the number of postcodes where the average crime rate has got worse.

Everything i have tried is counting all postcodes rather than just those where there have been improved patrols.

This is my latest attempt;

if( (avg(if([Date1] >= [Date2], [Crime Rate])))/1000 > (avg(if([Date1] <= [Date2], [Crime Rate])))/1000, count(distinct Postcode)

But this doesn't work, any ideas?

sunny_talwar

May be this

Count(DISTINCT {<Postcode = {"=Avg(If([Date1] >= [Date2], [Crime Rate])) > Avg(If([Date1] <= [Date2], [Crime Rate]))"}>} Postcode)