Trend Chart Using Date Range in a Set Analysis Expression

[alexandergrando]

I am trying to create a trend line chart with CalendarDate as the dimension and a COUNT of Active Properties as the expression.

I had to use a set analysis expression in order to COUNT the number of active properties on a given day:

=COUNT({<BegDateOfOperation = {'<=$(=CalendarDate)'}, EndDateOfOperation = {'>$(=CalendarDate)'}>}  %PropertyKey)

The problem is that all I get is either a straight line or a "No data to display" message when I try to create the chart. Attached is a screenshot. I would highly appreciate some advice.

[Anil_Babu_Samineni]

try to use only aggregation function

=COUNT(%PropertyKey)

[Anonymous]

Will you be able to share a sample qvw??

[vinieme12 · Accepted solution]

You need a RowKey that identifies each row uniquely for such intra record calculations or use IF()

Count(IF( BegDateOfOperation <= $(=CalendarDate) and EndDateOfOperation > $(=CalendarDate) , %PropertyKey))

OR

=COUNT({<RowKEY = {"=BegDateOfOperation <=$(CalendarDate) and  EndDateOfOperation >$(CalendarDate)"} >}  %PropertyKey)

Read this Post

set_analysis_intra-record.qvw