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: 
fginfrida
Partner - Contributor III
Partner - Contributor III

Qlik Sense: Show All Dates When Selecting a Dimension Value

Henric Cronstrom( hic‌) provided a way to include all master calendar dates in visualizations

Re: QlikSense Dimension: show all values like QlikView?

His solution is both simple and logical, adding a second term to the measure:

      Count(SERVICE_REQUEST)+0*Count({1}SERVICE_REQUEST)

The Set Analysis in the second term forces Qlik Sense to show all values, but since it's multiplied by zero, the value will still be the correct one.

I've been able to modify the measures in any chart visualization to allow the master calendar dates to display regardless of the selection that has been made.

However, I've a need to adjust QS behavior when a selection is made.  My app has a bar chart that reports on the number of service requests processed.  If a date range is selected, the measure Count(SERVICE_REQUEST)+0*Count({1}SERVICE_REQUEST) returns all dates in the master calendar (as it should).  Since the solution will have ~18 months of data, and the visualization requirement is to show the requests processed by individual for a given month, there is a need to limit the dimension to one month of that range (or less).

I experimented with the measure, trying:

          Count(SERVICE_REQUEST)+0*Count({$}SERVICE_REQUEST)

which gives the proper result when selecting a date range:

Selection.png

Chart1.png

When a selection is made (for example, selecting one of the individuals responsible for processing service requests), that expression loses the master calendar (also as expected)

Chart2.png

Can anyone suggest an expression for the measure that will retain the master calendar dates for a given date range when a selection is made?

Thanks in advance

Frank.

1 Solution

Accepted Solutions
fginfrida
Partner - Contributor III
Partner - Contributor III
Author

Henric Cronstrom has provided a solution to this issue:

I would probably try something like

Count(SERVICE_REQUEST) +

   0*Count({1<MonthYear={"$(=Date(Max(MonthYear),'MMM-YYYY'))"}>}SERVICE_REQUEST)


Then it would return all dates in the last month, irrespective of selection.


HIC

That worked perfectly once I adjusted the expression for the trend line to return only the selected values.

That was so because the expression that Mike Tarallo had posted in How to add lineal trend in Qlik sense:

linest_m(total aggr(if(sum(Sales),sum(Sales)),OrderDate),OrderDate)*

only({1}OrderDate)+linest_b(total aggr(if(sum(Sales),sum(Sales)),OrderDate),OrderDate)

returned all dates in the order clause.  Once I changed that to only({$}OrderDate) the combo chart worked properly.

Frank.

View solution in original post

1 Reply
fginfrida
Partner - Contributor III
Partner - Contributor III
Author

Henric Cronstrom has provided a solution to this issue:

I would probably try something like

Count(SERVICE_REQUEST) +

   0*Count({1<MonthYear={"$(=Date(Max(MonthYear),'MMM-YYYY'))"}>}SERVICE_REQUEST)


Then it would return all dates in the last month, irrespective of selection.


HIC

That worked perfectly once I adjusted the expression for the trend line to return only the selected values.

That was so because the expression that Mike Tarallo had posted in How to add lineal trend in Qlik sense:

linest_m(total aggr(if(sum(Sales),sum(Sales)),OrderDate),OrderDate)*

only({1}OrderDate)+linest_b(total aggr(if(sum(Sales),sum(Sales)),OrderDate),OrderDate)

returned all dates in the order clause.  Once I changed that to only({$}OrderDate) the combo chart worked properly.

Frank.