7 Replies Latest reply: May 19, 2015 10:17 AM by Patrick Portefaix RSS

    How selected value of a date filter could be reused in two different date dimensions?

       

      Hi all,

       

      I’m new to Qlik Sense. I’m working to prepare Sales reporting.

       

      I have a table with sales order information, including booking date, and a table with billings information, including an billing date. The two tables are linked by an ID_Mission field. No problems so far.

       

      I would like to have a unique filter that would allow to filter on Year and this selected year would be reused as dimension on graphics that are in different sheets using either Booking date or Billing date.

       

      I would select eg 2015 once for all, and moving from sheet to another, it would filter on 2015 Booking Dates or on 2015 Billing dates, depending on the graphs. It would save having to cancel the unappropriated filter and select another.

       

      I’ve tried to use the GetFieldSelections with no luck. I succeeded in retrieving the year selection, but I can’t find a way to pass that value to the SaleDate dimension or to the BillingDate dimension which I use in my graphs.

       

      Thank you in advance for your help.

       

      Patrick

       

        • Re: How selected value of a date filter could be reused in two different date dimensions?
          Jonathan Poole

          You could create an 'unassociated' year field that loads all the years from the billing and booking dates with this script in the load editor

           

          -----------------------

          Years:

          Load distinct

               Year(BillingDate) as Year

               ...

          from ...

           

          concatenate (Years)

          Load distinct

               Year(BookingDate) as Year

          from ...

          where not exists(Year);

          -----------------------

           

          Then,  you can filter individual charts by adding a filter via a set statement, which you would put inside the chart aggregation function in each measure within the chart

           

          Measure:   Sum(Sales)  -> disregrads selections on Year

           

          Measure:   Sum( {<BillingYear=p(Year)>}  Sales)  -> filters the chart so that only billing years from the year selection are used


          Measure:   Sum( {<BookingYear=p(Year)>}  Sales)  -> filters the chart so that only booking years from the year selection are used