Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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])
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?
May be this
Count(DISTINCT {<Postcode = {"=Avg(If([Date1] >= [Date2], [Crime Rate])) > Avg(If([Date1] <= [Date2], [Crime Rate]))"}>} Postcode)