Date1 > Date 2 Set analysis

[Chewitt]

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.

[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]

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)