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

[#1]

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

[JonnyPoole · Accepted solution]

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

[#3]

Hi Jonathan,

Many thanks for your help on this. Your proposal did work and I’m now able to filter by using a unique dimension YearMonth and my different graphs are using the BookingDate or BillingDate appropriately. Thanks again!

I’ve been able to load the year data from your script, but I’ve not been able to load Month in the same load… I had to do year and month separately. There is probably room for optimisation, if someone as ideas on this…

I'm also not sure about the way the newly created table is named, and how to give it a name I choose.

Here is the script I’m currently using:

Load distinct

     Date("BillingDate") as Year

     from [lib://Offre Reporting/Base AH.xlsx]

(ooxml, embedded labels, table is Facturation);

concatenate

Load distinct

     Date("BookingDate") as Year

     from [lib://Offre Reporting/Base AH.xlsx]

(ooxml, embedded labels, table is Missions);

concatenate

Load distinct

     Date("ProposalDate") as Year

     from [lib://Offre Reporting/Base AH.xlsx]

(ooxml, embedded labels, table is Missions);

Load distinct

      Month("BillingDate") as Month

     from [lib://Offre Reporting/Base AH.xlsx]

(ooxml, embedded labels, table is Facturation);

concatenate

Load distinct

     Month("BookingDate") as Month

(ooxml, embedded labels, table is Missions);

concatenate

Load distinct

     Month("ProposalDate") as Month

     from [lib://Offre Reporting/Base AH.xlsx]

(ooxml, embedded labels, table is Missions);

Last thing is your help allowed me to better understand the help section http://help.qlik.com/sense/en-US/online/#../Subsystems/Hub/Content/ChartFunctions/SetAnalysis/SetAna...

I will continue working on this.

Regards,

Patrick

[JonnyPoole]

Hi Patrick - here is a sample with your  code for Year and month. I've named the table as well:

YearMonthTable:

Load distinct

     Year("BillingDate") as Year,

     Month("BillingDate") as Month

  from [lib://Offre Reporting/Base AH.xlsx] (ooxml, embedded labels, table is Facturation);

concatenate (YearMonthTable)

Load distinct

     Year("BookingDate") as Year,

     Month("BookingDate") as Month

from [lib://Offre Reporting/Base AH.xlsx] (ooxml, embedded labels, table is Missions);

concatenate (YearMonthTable)

Load distinct

     Year("ProposalDate") as Year,

     Month("ProposalDate") as Month

from [lib://Offre Reporting/Base AH.xlsx] (ooxml, embedded labels, table is Missions);

[#5]

Hi Jonathan,

Many thanks for your optimization of my loading script. I have implemented your script and it did work great!

I have other problems now but they are not related to this discussion and I guess I should better raise them on a new discussion.

Maybe we will get in touch again!

Your help has been greatly appreciated.

Patrick

[JonnyPoole]

super !  i noticed the thread is still listed as 'not answered' . If you are good , consider closing the thread marking any replies as helpful/correct to help others find answers to their questions.

[#7]

Hi Jonathan,

Yeas, I'm all good now, and using extensively your solution for filtering booking or billing year from a year filter by using your:

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

Thanks a lot for your help.

Best regards,

Patrick

[#8]

Hi Jon,

I have marked your solution as « useful », but I couldn’t find a way to mark it as “resolved”… Not sure if there are 2 different actions or just one… You’ll tell me…

Best regards,

Patrick

De : Jonathan Poole

Envoyé : mardi 19 mai 2015 15:56

À : Patrick Portefaix

Objet : Re: - How selected value of a date filter could be reused in two different date dimensions?

<https://community.qlik.com/resources/images/palette-1004/headerLogo-1391206184624-QlikCommunity_logo.gif>