topic Trend Chart Using Date Range in a Set Analysis Expression in QlikView

Trend Chart Using Date Range in a Set Analysis Expression

alexandergrando — Thu, 13 Apr 2017 22:04:29 GMT

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.

Re: Trend Chart Using Date Range in a Set Analysis Expression

Anil_Babu_Samineni — Fri, 14 Apr 2017 02:08:56 GMT

try to use only aggregation function

=COUNT(%PropertyKey)

Re: Trend Chart Using Date Range in a Set Analysis Expression

Anonymous — Fri, 14 Apr 2017 05:04:14 GMT

Will you be able to share a sample qvw??

Re: Trend Chart Using Date Range in a Set Analysis Expression

vinieme12 — Fri, 14 Apr 2017 07:01:49 GMT

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))

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

Read this Post

set_analysis_intra-record.qvw