Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
Thank you Vegar!