1 Reply Latest reply: Jan 23, 2018 2:06 PM by Frank Ginfrida RSS

    Qlik Sense: Show All Dates When Selecting a Dimension Value

    Frank Ginfrida

      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.

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

          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.