1 2 Previous Next 21 Replies Latest reply on Jul 19, 2018 2:44 AM by Amit Narkar

# Calculation field to compare day before

Hi all.

Based on my current calculation field, I noticed that if I have a missing day...the variance wont perform.

* ps: this calculation field is taught by ZhouYi Zhang. Due to urgency, I make a new post..

May i know how to make it to compare toward the previous available day?

For eg :

If  TODAY is 26/6, the differences shown should be (500-1000)/1000

Thanks

• ###### 1. Re: Calculation field to compare day before

You are trying to calculate % difference. In tableau we do have Table Calculations which can be used for the same.

• ###### 2. Re: Calculation field to compare day before

Dear Amit

However, it cannot be done if I add in a date filter.

For example, if I select 1 particular date...the differences value using Table Calculation is not appearing...

Any other suggestion ?

• ###### 3. Re: Calculation field to compare day before

With existing solution you need to update your date filter.

IF [Customized Date] = 'Default' THEN

[Date] = TODAY() or [Date]={ max(If [Date] < TODAY() then [Date] end)}

ELSE

[Date] = [Customized Date Select] or [Date]={ max(If [Date] < [Customized Date Select] then [Date] end)}

END

This filters considers date selected by user and the previous date with data available.

Earlier you logic was checking date and date-1.

Date -1 may not have data, which will be corrected by above logic.

1 of 1 people found this helpful
• ###### 4. Re: Calculation field to compare day before

Hi,

As you are using a division formula you do not have the ELSE part in the calculation.

SUM(IF [Date]<>[max date] THEN [Amount] ELSE [Amount Call] END

Try with this.

Regards

Sohan.

1 of 1 people found this helpful
• ###### 5. Re: Calculation field to compare day before

Dear Amit

It helps! thanks!

• ###### 6. Re: Calculation field to compare day before

Dear Amit

May I know how to perform I do not need a selection of today in the workbook? But only customized date selected by user

Means if the user choose a particular date...the differences shown is the differences of day selected and day before (selected)

Thanks

• ###### 7. Re: Calculation field to compare day before

IF [Customized Date] = 'Default' THEN

[Date] = TODAY() or [Date]={ max(If [Date] < TODAY() then [Date] end)}

ELSE

[Date] = [Customized Date Select] or [Date]={ max(If [Date] < [Customized Date Select] then [Date] end)}

END

for a custom date select it will give difference between date selected and earlier date for which data is available.

This earlier date may not be same as date-1 if there are no records with date-1

[Date]={ max(If [Date] < [Customized Date Select] then [Date] end)}

1 of 1 people found this helpful
• ###### 8. Re: Calculation field to compare day before

Dear Amit

Kindly refer to the file attached. May i know where is my error?

80000

2.077

Thanks

• ###### 9. Re: Calculation field to compare day before

Your earlier version was more accurate. You just need to use filter details as shared earlier.

• ###### 10. Re: Calculation field to compare day before

Dear Amit

Thanks...As I am trying to decrease the parameter and calculation field used...so i come out with the latest version...haha

• ###### 11. Re: Calculation field to compare day before

Dear Amit

May i know for my the 'sales cal' calculation field

The highlighted field cannot be in a calculation?  for ((eg a/2)/b)

Thanks

• ###### 12. Re: Calculation field to compare day before

Dear Amit Narkar

With the calculation fields we discussed earlier...i realized a problem...Why there is 2 dates over there when i select a date? Is there is solution?

Thanks

• ###### 13. Re: Calculation field to compare day before

In my earlier solution as well I have used Max Date, so you don't see 2 dates.

• ###### 14. Re: Calculation field to compare day before

Dear Amit

Do you mean I need to add the max date at the column as well?

And another question is, I realized that if the there is data in the previous selected date...the measure name will appear..Bangsar is the example...it has no data on 1/10/18 but it will appear...but other measure names with no data will not appear...because the there is no data ahead of 1/10/18.

thanks

1 2 Previous Next