1 2 Previous Next 24 Replies Latest reply on Jan 10, 2018 1:45 PM by Jagjit Singh Go to original post
• ###### 15. Re: Calculate No of Days between Teams within Month

Hello Jagjit,

Fixed.

Regards

• ###### 16. Re: Calculate No of Days between Teams within Month

Hi Lei,

That worked fine but when I used the calculation again my production data, i'm getting negative [Days with Assets] and not sure why these are appearing as the dates look fine.

I have attached the spreadsheet with negative days.

Thanks

Jag

• ###### 17. Re: Calculate No of Days between Teams within Month

Hello Jagjit,

Calculation logic reorganized.

But one question,

How would you calculate "Days with Assets" for this line?

 Prop Code Property Month End Void Start Date Accepted by Assets Referred to Allocations Accepted by Allocations Let Date 3611 2015/08/31 2015/07/29 2015/08/19 2015/08/09 2015/01/09 2015/09/15

Regards.

• ###### 18. Re: Calculate No of Days between Teams within Month

Hi Lei,

Thanks again. I have put my comments in the attached excel file for this case.

Regards,

Jag

• ###### 19. Re: Calculate No of Days between Teams within Month

Hello Jagjit,

Modified.

Since the logic is complicated, some of the if/iif in the calculation is written to match certain rows.

I'm not sure how the modification will affect the result of other rows.

Please check the calculation on your whole data as carefully as possible.

Regards

• ###### 20. Re: Calculate No of Days between Teams within Month

Hi Lei,

Yeah it is a bit complicated and still getting negatives and wrong day counts. By the way, do we really need to compare [Accepted by Assets], [Referred to Allocations] and [Accepted by Allocation] to [Void Start Date].

For example what if we check  [Accepted by Assets] is in between Month Start and Month End date. If it is we date diff between [Accepted by Assets] - [Referred to Allocations].

Will this logic work?

Regards,

Jag

• ###### 21. Re: Calculate No of Days between Teams within Month

Hi Lei,

I have posted the same query on the SQL forum and getting more accurate results using the SQL query. Still like to achieve the same in Tableau. Have a look at the link below and hope this helps.

jag

• ###### 22. Re: Calculate No of Days between Teams within Month

Hello Jagjit,

I think it's time to review the total logic

• Check null date

1st condition: is [Void Start Date] null

Yes -> 0

No -> continue with the 2nd condition

2nd condition: is [Accepted by Assets] null? (Here, [Void Start Date] not null)

Yes -> continue with the 3rd condition.

No -> continue with the 5th condition.

3rd condition: is [Referred to Allocations] null? (Here [Accepted by Assets] null, [Void Start Date] not null)

Yes -> continue with the 4th condition.

No -> not defined.

4th condition: compare [Void Start Date] and today

Void > today -> 0

Void <= today -> date difference from Void to today.

5th condition: is [Referred to Allocations] null? (Here, Neither [Accepted by Assets] or [Void Start Date] is null)

Yes -> continue with 6th condition

No -> all the three days not null case

6th condition: compare today with max([Void Start Date], [Accepted by Assets])

today < max -> 0

today >= max -> date difference from max to today.

Sorry that I don't have time from now on today, so I'd like to continue with the "all the three days not null case" logic tomorrow.

Before that, please confirm the above "check null date" logic.

Regards

• ###### 23. Re: Calculate No of Days between Teams within Month

Hello Jagjit,

For the "all the three days not null case", I tried hard to make the logic clear, but to find out that there are too many cases.

There are six dates affecting the calculation,

1) Month Start

2) Month End (=Property Month End)

3) Today

4) Void Start Date

5) Accepted by Assets

6) Referred to Allocations

Assumed 4=5 and 4<6, there have already been 21 possibilities.

There are too many permutations with them all.

In the calculated field for days with assets, I only listed the cases you listed before, while more cases are not defined or not defined precisely,

which results in some results not correct while testing production data.

My advice you that,

1) make a list of all the possible permutations

2) filter out the not existing permutations

3) figure out the calculation logic for each permutation

Then we can start writing Tableau calculation field.

Regards.

• ###### 24. Re: Calculate No of Days between Teams within Month

Hi Lei,

Apologies for coming back to this after a long time. As per the above request the below are possible permutations:

Flow of property movement: Void Start Date > Referred to Assets> Referred to Allocations>Let Date

@

Case 1: All the dates can be the same if the property is repaired and let same day. In this case property was with assets and allocation 1 day

Case 2: Referred to Assets & Referred to Allocation can be greater than void start date

Case 3: Void Start Date & Referred to Assets are same day and referred to allocation is greater than Void Start Date & Referred to Assets

Case 4: Let date can be blank for few months. In this case Days with allocation is the full month or Referred to Allocations - End of Month

Case 5: When Referred to Allocation day is within the month and Referred to Assets date is in the previous month and let date is blank

a) Days with assets is Start Month - Referred to Allocations

b) Days with Allocations is Referred to Allocations - End of Month

I have attached sample workbook and desired result in the excel file

Thanks

Jag

1 2 Previous Next