12 Replies Latest reply on Jan 14, 2019 2:16 PM by Yuriy Fal

# Apply 2 Different Selections of 1 Filter to the Same Measure

Situation: 3 Dashboard filters, namely Date (relative date filter), Error Code, and Aggregator applied to multiple worksheets. This worksheet has a graph that displays number of users over days.

Objective: To have 2 lines overlayed on this graph (i.e. dual-axis.) The first one (showed below) representing number of users over date selected, and another representing number of users over date period before that.

Example: If relative date chosen is "Last 6 days" - the 1 line should show the most recent 6 days of "UserCount", while the 2nd line should show the 6-day period before that (i.e. 12-6 days ago) for "UserCount"

What I have currently:

1. Calculated field called "DateSelected": DATEDIFF('day', MIN([Date]),MAX([Date])) + 2))

-i.e. if "Last 6 days" is selected then DateSelected = 6 (added +2 as for some reason it was always 2 days under)

2. Calculated field called "CurrPeriodUsers": IF [Date] < (TODAY() - [DateSelected] + 1) OR [Date]>(TODAY() + 1) then false else true end

- ie Show date if it's between 6 days ago and today

3. Calculated field called "LastPeriodUsers": IF [Date] < (TODAY() - [DateSelected]*2 + 1) OR [Date]>(TODAY() - [DateSelected]+ 1) then false else true end

-i.e. Show date if it's between 12- 6 days ago (side note: I get error "cannot mix aggregate and non-aggregate functions even though "DateSelected" is an integer)

- but I don't know how to apply "CurrPeriodUsers" and "LastPeriodUsers" simultaneously to "UsersCount" to get 2 different lines. I think I have to use LOD expressions but I don't properly understand them yet (tried with turning "DateSelected" into a proper date (instead of an integer) and having {fixed [Error Code], [Aggregator], [Date]: sum([UsersCount])} and then another LOD swapping out [Date] for [DateSelected].)

• ###### 1. Re: Apply 2 Different Selections of 1 Filter to the Same Measure

HI Steffi,

You may want to simplify your problem a bit

to make a solution in Tableau simple as well.

What if your Relative Date Filter would be set

using the following (mental) rule:

-- the Date Range would be an even number of Days

the size of both Current and Previous periods together.

If you compare 7 days, the filter should be set to 14.

With the filter set according to the rule, one could simply

divide the whole range into two periods of the same size.

This would be a Dimension -- to make a view simple to build.

Note that for the solution to work as expected

your data should be 'dense' enough.

At least there should be rows in the data (Marks on a view)

for the first and the last days of the filtered Date Range.

This is needed to calculate the exact same size of Curr & Prev.

Besides, for a 'dual-axis' effect on a view,

one could simply 'shift' the dates for the Prev.

Please find the attached as an example.

Hope it could help a bit.

Yours,

Yuri

1 of 1 people found this helpful
• ###### 2. Re: Apply 2 Different Selections of 1 Filter to the Same Measure

Thanks for explaining your method; it's interesting how we all approach a problem differently.

Would you mind attaching the workbook again as I don't see it?

Kind Regards

• ###### 3. Re: Apply 2 Different Selections of 1 Filter to the Same Measure

Hi Steffi,

The attached workbook is there.

and look at the lower left of my reply.

Yours,

Yuri

1 of 1 people found this helpful
• ###### 4. Re: Apply 2 Different Selections of 1 Filter to the Same Measure

Perfect, that's exactly what I was looking for! Sorry that I didn't see the workbook earlier.

The only issue is that my relative date field is set to continuous (same as the Order Date in your example), yet only one line shows.

However when the relative date is set to discrete (ie not relative anymore) then the graph displays with 2 lines as desired.

So how do I show both lines whilst having a continuous (and relative) date filter?

• ###### 5. Re: Apply 2 Different Selections of 1 Filter to the Same Measure

Oh I realized that the date filter has to be Added to Context.

Fixed, thank you very much!

• ###### 6. Re: Apply 2 Different Selections of 1 Filter to the Same Measure

Steffi, you're welcome.

• ###### 7. Re: Apply 2 Different Selections of 1 Filter to the Same Measure

I'm having a similar issue.  I want to be able to have the user select their date from the "Relative Date" Filter option.  Based on what they would select - lets say - previous Month, I need a calculation that would show the Last 7 Days from that filter.

I have this calculation for the Last 7 Days: DATEDIFF('day', [Filter Date] , {MAX([Filter Date])} ) <= 6

Filter Date is just the Date Filter.  This works correctly when any user selects "Month to Date", or Last 30 days etc, but if you select Previous Month, the calculation will not work.  I want to the same to show Last 28 days Trailing as well and that doesn't work (it is not going back to the month of December).

Any suggestions?  Or is just not possible to use the "Relative" date filter w/ this type of formula.

• ###### 8. Re: Apply 2 Different Selections of 1 Filter to the Same Measure

Hi Kelly,

The Relative Filter put in Context should work as expected:

Please find the attached.

Yours,

Yuri

• ###### 9. Re: Apply 2 Different Selections of 1 Filter to the Same Measure

It’s marking them all as False for me

See Attachment of screen shot:

• ###### 10. Re: Apply 2 Different Selections of 1 Filter to the Same Measure

Hi Kelly,

The [Date] Relative Filter should be made a Context one

(right-click on the pill in the Filters pane --> Add to Context).

Context Filters are applied before FIXED LOD expressions:

Yours,

Yuri

• ###### 11. Re: Apply 2 Different Selections of 1 Filter to the Same Measure

Thanks, Yuriy.  That did the trick

• ###### 12. Re: Apply 2 Different Selections of 1 Filter to the Same Measure

Kelly, you're welcome.