Qlik Sense: Show All Dates When Selecting a Dimension Value

fginfrida — Tue, 23 Jan 2018 15:57:25 GMT

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:

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)

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.

Re: Qlik Sense: Show All Dates When Selecting a Dimension Value

fginfrida — Tue, 23 Jan 2018 19:06:19 GMT

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.

topic Re: Qlik Sense: Show All Dates When Selecting a Dimension Value in App Development

Qlik Sense: Show All Dates When Selecting a Dimension Value

Re: Qlik Sense: Show All Dates When Selecting a Dimension Value