I am trying to create a single table in looker where some of the measures are adjusted with a page date filter and some measures in same table are not. is that possible?
Page date filter would be “Month to day”.
|Area||Invoiced Revenue||Invoiced revenue last 5 days|
if i then change date filter on page to “yesterday”
Then invoiced revenue changes but invoiced revenue last 5 days is still the same
One way i can think of fixing this now is to have this in two tables, where date filter affect on but not the other. I do not find that to give a good user experience from and overview perspective and then you would not be able to sort on column and see effect on the other
Other option is to have a merge query where date filter does not apply to one of the merge, but as i understand i should always try to avoid merge querys
Any good ideas ?
all help is much appreciated.
From my understanding there is no way to do what you are asking. the filters will eliminate the data from the query so there will be way to access the information in them in another measure. I get around this by having the merged query with asymmetric filtering that you mentioned or many measures in one table that would give the user most of the realistic values they need (last 5 day, last 2 weeks, last month...)
I’d create a NDT that doesn’t bind all filters - just the ones you want applied to the specific columns.