4 Replies Latest reply on May 29, 2017 2:35 AM by Norbert Maijoor

# Filtering dates in a table-calculation

Dear all,

I have been working on a project where I need to calculate the percent difference between a date and a date 364 days before. Easy enough with the table-calculation "Percent of difference", which works like a charm "(ZN(SUM([Amount])) - LOOKUP(ZN(SUM([Amount])), -364)) / ABS(LOOKUP(ZN(SUM([Amount])), -364))".

However, now I only want to show the last month in the table. And there the fun starts. As soon as I filter I am loosing the table-calculation, since I am filtering the base which is used in the calculation.

Attached is a sample workbook, showing the table-calculation.

Anybody has a brilliant idea how to overcome this?

Thank you already, putting me on the right track here.

Peter

• ###### 1. Re: Filtering dates in a table-calculation

I am using Table Calculation and Addressing field to achieve the same.

Are you looking for the same ?

Thanks,

Ritesh

• ###### 2. Re: Filtering dates in a table-calculation

Hi Peter,

Is that what you wanted?

Workbook attached for your reference. Let us know if you've any query.

Mahfooj

• ###### 3. Re: Filtering dates in a table-calculation

For Percent Diff just chose the other option ....

• ###### 4. Re: Filtering dates in a table-calculation

Hi Peter,

Find my approach as reference below and stored in attached workbook version 10.2 located in the original thread

1. Define Days Current Month

2. CY: if DATEDIFF('month',[Date],today())=0 and DATEDIFF('year',[Date],today())=0 then [Amount] END

3. PY: if DATEDIFF('month',[Date],today())=11 and DATEDIFF('year',[Date],today())=1  then [Amount] END

4. % Difference: (sum([CY])-sum([PY ]))/sum([CY])

5. Drag the required object to the indicated positions.

Regards,

Norbert

1 of 1 people found this helpful