Skip to main content
Announcements
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
alexandergrando
Contributor III
Contributor III

Trend Chart Using Date Range in a Set Analysis Expression

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.


Trend Capture.PNG

1 Solution

Accepted Solutions
vinieme12
Champion III
Champion III

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




Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

3 Replies
Anil_Babu_Samineni

try to use only aggregation function

=COUNT(%PropertyKey)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Anonymous
Not applicable

Will you be able to share a sample qvw??

vinieme12
Champion III
Champion III

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




Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.