6 Replies Latest reply on Sep 4, 2018 4:10 AM by Manon Grest

# Calculate a target which ignore filters??

Hello !

I would like to calculate a target about lead time based on the previous months (from January 2018 to June 2018).

'A40' is the name of the dimension where there are dates and 'A40-A80' is the name of my lead time, it is a measure.

What I would like is to calculate the target like this :

IF A40 > 1st January 2018 AND A40 < 30th June 2018 THEN

AVG(A40-A80) END

The point is that I would like to display on a same sheet the target (which is fixed after calculation) and the avg(A40-A80) over the last 7 months.

The problem is that my filter which is 'over the last 7 month' (which is currently from March to September because it is dynamic) impacts my target because it must be calculated from January to June.

How can I do, to overpass my filter to calculate the target ?

• ###### 1. Re: Calculate a target which ignore filters??

Hi Manon,

Wrap your Calculation with Curly Braces like below

{

IF A40 > 1st January 2018 AND A40 < 30th June 2018 THEN

AVG(A40-A80) END

}

So that your calculation wont be affected by the filter and this is calculation at overall level of data. If its need to fixed to dimension then use your calc like below

{Fixed <Dimension your aggregation need to happen>:

IF A40 > 1st January 2018 AND A40 < 30th June 2018 THEN

AVG(A40-A80) END

}

Hope this helps.

If it didn't help kindly attach a workbook with sample data.

BR,

NB

• ###### 2. Re: Calculate a target which ignore filters??

Hello Naveen:

I tried what you said but in both cases there is an aggragation problem.. (Start date and End date are parameters which represent 1st January and 31th June)

Unfortunately, I can not share my file because of sensitive data. :/

• ###### 3. Re: Calculate a target which ignore filters??

{if attr(A40) > [Start Date] and attr(a40) < [End Date]

then

AVG([A40-A80 (CMD)]) END)

BR,

NB

• ###### 4. Re: Calculate a target which ignore filters??

Sorry but it does not work because you can not put 'ATTR' function when you use LOD

• ###### 5. Re: Calculate a target which ignore filters??

AVG({if (A40) > [Start Date] and (a40) < [End Date]

then

([A40-A80 (CMD)]) END))

This will do

BR,

NB

• ###### 6. Re: Calculate a target which ignore filters??

It still does not work.. It seems it must have a function (exclude ATTR) before the A40-A80.

I tried to get you a rough tableau file with the data I am working on. Hope it will help you anyway.

P.S : I made an update of this message at 13:08 today with a new twbx be sure you got the right one.

Thanks