7 Replies Latest reply on Jun 13, 2018 8:24 AM by Shinichiro Murakami

# How to keep count Fixed To Two Dimension for Previous Value

Hi All,

This Discussion is continuation of below thread.  If I change the parameters, previous value should remain constant .

As Joe suggested I do get the Previous value by keeping it fixed.

How ever, If I filter by region, then count is different.

So, in this example, If i filter by Region East, Previous quantity shows 4 for 4/17/2017 where as it should be 1(worksheet-Quantity-2).

My calculation should be in two steps.

1. Calculation  [Prior quantity -Fixed] -  { FIXED [Order Date],[Region]:COUNTD([Quantity])}

2.   { FIXED : COUNTD( if [Order Date] >= Dateadd( [Select Period], -1, DATETRUNC([Select Period],[Date Selection]))

and [Order Date] <= dateadd( [Select Period], -1 , [Date Selection]) then

[Prior quantity -Fixed] end) }

This calculation doesnt seem to work.

• ###### 1. Re: How to keep count Fixed To Two Dimension for Previous Value

Hi Susheela

Not exactly sure but you can get right results with context fuilter?

Overview: Level of Detail Expressions

## Filters and Level of Detail Expressions

There are several different kinds of filters in Tableau and they get executed in the following order from top to bottom.

The text on the right shows where level of detail expressions are evaluated in this sequence.

Extract Filters (in orange) are only relevant if you’re creating a Tableau Extract from a data source. Table calculations filters (dark blue) are applied after calculations are executed and therefore hide marks without filtering out the underlying data used in the calculations.

If you’re familiar with SQL, you can think of measure filters as equivalent to the HAVING clause in a query, and dimension filters as equivalent to the WHERE clause.

FIXED calculations are applied before dimension filters, so unless you promote the fields on your Filter shelf to Improve View Performance with Context Filters, they will be ignored. For example, consider if you have the following calculation on one shelf in a view, along with [State] on a different shelf:

`SUM([Sales]) / ATTR({FIXED : SUM([Sales])})`

This calculation will give you the ratio of a state’s sales to total sales.

If you then put [State] on the Filters shelf to hide some of the states, the filter will affect only the numerator in the calculation. Since the denominator is a FIXED level of detail expression, it will still divide the sales for the states still in the view against the total sales for all states—including the ones that have been filtered out of the view.

INCLUDE and EXCLUDE level of detail expressions are considered after Dimension filters. So if you want filters to apply to your FIXED level of detail expression but don’t want to use Context Filters, consider rewriting them as INCLUDE or EXCLUDE expressions.

Thanks,

Shin

• ###### 2. Re: How to keep count Fixed To Two Dimension for Previous Value

Hi,

thank you very much for this detailed explanation. this is helpful.

how ever, in the context filter, when we chaneg the region to West, it still gives 1 where as result is 7.

Any thoughts on that?

• ###### 3. Re: How to keep count Fixed To Two Dimension for Previous Value

I am not sure what you want to accomplish here.

Could you explain you expecting logic?

Thanks,

Shin

• ###### 4. Re: How to keep count Fixed To Two Dimension for Previous Value

This Logic is to keep the previous value fixed even if we change the date parameter.

• ###### 5. Re: How to keep count Fixed To Two Dimension for Previous Value

Hi Susheela,

Still not quite sure, but is this what you expect?

Shin

• ###### 6. Re: How to keep count Fixed To Two Dimension for Previous Value

Thank you :-)

Yes I forgot to change the field as prior quantity

• ###### 7. Re: How to keep count Fixed To Two Dimension for Previous Value

HI Susheela,

You are welcome.

Just a clarification, what I did was

Shin