10 Replies Latest reply on Apr 2, 2013 7:58 AM by Omar Sh

# Comparing a data field on different dates? Need Help desperately

I really need help in creating a calculated filed that would compare a field on different as of dates and do the follwoing:

IF num of Days > 30 and number of days on (date – 1) < 30  THEN 1 ELSE 0 END.

Below is an example of how it would work.  Thanks

Date                      # of  Days                                Calc Field I need

Mar 1                     28                                             0

Mar 2                     29                                             0

Mar 3                     30                                             1

Mar 4                     31                                             0

Mar 5                     32                                             0

Mar 6                     28                                             0

Mar 7                     29                                             0

Mar 8                     30                                             1

Mar 9                     31                                             0

• ###### 1. Re: Comparing a data field on different dates? Need Help desperately

Omar--

This should be doable. For the solution I am proposing, the table would have to look as it does above.

IF sum([# of Days])>30 AND lookup(sum([# of Days]),-1)<30 THEN 1 ELSE 0 END

The lookup function is capable of finding information in other rows in a table as it is a table calculation function. This allows us to use the results we get back from the data base and run another calculation on top of it.

Hope this helps,

Dan

• ###### 2. Re: Comparing a data field on different dates? Need Help desperately

Dan, Appreciate your response.

I think your solution would work if was a small table. I need to do this on a huge data set comprised two years of daily data.  I did test it out and it didnt work.  May be I am doing something wrong?

Thanks.

• ###### 3. Re: Comparing a data field on different dates? Need Help desperately

Unfortunately Omar, it is hard for me to know why it would or wouldn't work without seeing the data. Is there anyway you could upload a workbook with a small sample of the data so that I could test out my solution. If I get something working, I can then reupload it for you to replicate what I did in your environment.

As a follow up, is the goal here to sum up the calculated fields at the month level?

Dan

• ###### 4. Re: Comparing a data field on different dates? Need Help desperately

Sample file attached.

Field call 'how#' is the one i created based on your input. However, i may be doing something wrong. It is not working.

• ###### 5. Re: Comparing a data field on different dates? Need Help desperately

Omar--

I need you to re-save the workbook as a packaged workbook. Please go to File --> save as and select Tableau Packaged workbook and then upload that here. This packages up the extract with the workbook itself.

Thanks,

Dan

• ###### 6. Re: Comparing a data field on different dates? Need Help desperately

Thanks Dan. Appreciate the help.  See attached.

• ###### 7. Re: Comparing a data field on different dates? Need Help desperately

Omar--

The calculation is working just fine. The problem is the conditions of your calculation. There is no row in the table which has a value of greater than 30 AND with the previous row being less than 30. If you change the logic in my calculation to IF sum([# of Days]) >= 30 AND lookup(sum([# of Days]),-1)<30 THEN 1 ELSE 0 END then you will see some 1s as the conditions will be met.

Hope this helps,

Dan

• ###### 8. Re: Comparing a data field on different dates? Need Help desperately

Thank you so much. It was so simple for you to see but I could not figure it out.  That does work. The only part which is still not working is that sum.  How can I sum the 1s in the created calculated field.

I can not thank you enogh for you time and help.

• ###### 9. Re: Comparing a data field on different dates? Need Help desperately

Omar--

Please see the attached. I have added a bunch of instructions into the captions on the three sheets.

Hope this helps,

Dan

• ###### 10. Re: Comparing a data field on different dates? Need Help desperately

Dan, This is very useful and a great help.  Thank you for being so patient with me.  Really appreciate your time and help.

Thanks a lot.