How to Restrict Particular Metric with custom date Filter

Question & Answer
, ,
1

I have created the custom date filter like, last 1 year, last 2 years, last 5 years and current month.

i have created calculated field like
sum(ifelse(Date field > addDateTime(-12,‘MM’,${date}) AND Date field <= ${date} AND {metric_desc}=‘Occupancy’,value1,NULL)) /
sum(ifelse(Date field > addDateTime(-12,‘MM’,${date}) AND Date field <= ${date} AND {metric_desc}=‘Occupancy’,value2,NULL))

in metric_desc we have like

image

In this i am trying to get the data for last 12 months restricing the data only to Occupancy, when i am trying to do using the above calculation not able to get any data.
Please help me out to solve the above issue.

2

Instead of NULL can you try 0

3

tried with 0 but output is no value where is some value in our report

4

Instead of subtracting dates you need to add.

To get the previous years date you need to add it to the previous year so that it matches up with the current one.

sum(ifelse(Date field > addDateTime(12,‘MM’,${date}) AND Date field <= ${date} AND {metric_desc}=‘Occupancy’,value1,NULL)) /
sum(ifelse(Date field > addDateTime(12,‘MM’,${date}) AND Date field <= ${date} AND {metric_desc}=‘Occupancy’,value2,NULL))