Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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)

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
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.