topic Re: Exclude dimension values from table based on dynamic date selection in QlikView

Exclude dimension values from table based on dynamic date selection

ellenblackwell — Sat, 13 Apr 2019 08:52:10 GMT

Hello Community,

I am trying to resolve a display issue that is driven by the time period selected by a user. Expression results based on the time period are working correctly. However, I am having trouble getting the proper dimension values to display based on the time period selected. How can I exclude Performer B/Organization 2 from the table below ?

Time Period Selected :	Full Prior Month :	3/1/19 - 3/31/19

	Performer	Organization	Org Activated Date	Org Deactivated Date	Transactions During Time Period Selected	Display Performer?
	A	1	3/15/2019	12/31/9999	0	Yes, because org active during a portion of the time period selected - regardless of 0 value in expression results
	B	2	1/1/2019	2/15/2019	0	I want to exclude this row, because org was deactivated before min date in time period selected. But if I suppress 0 values, I lose the row above, too.
	C	3	1/1/2019	3/15/2019	12	Yes, because org active during a portion of the time period selected
	D	4	1/1/2019	4/5/2019	17	Yes, because org active over the full time period selected

Many thanks in advance!

Ellen

Re: Exclude dimension values from table based on dynamic date selection

Vegar — Sat, 13 Apr 2019 18:17:16 GMT

You need a loose calendar/date table. In my example the calendar contains a field called Date and a field called Month (among others).

Expression

sum({< [Org Activated Date] = {"<=$(=max(Date))"}, 
       [Org Deactivated Date] = {">=$(=min(Date))"}>}1)

You could of cource have another measure than SUM(1), but as long you keep this SET Modifier you will only get hits within the Org Activate-Deactivate period.

Re: Exclude dimension values from table based on dynamic date selection

ellenblackwell — Tue, 16 Apr 2019 06:43:26 GMT

Thank you Vegar!