11 Replies Latest reply on Mar 26, 2017 1:09 AM by ammar.khwaira

# LOD with Two date columns

Hi

for each week of creation_date, i would like to count number of issues created and number of "Open issues" till the end of that week.

(open issues is basically the sum of counter) , if i would write this  in sql for a certain week , i woul say :

select  sum(counter) as still_open

from    table a

where   creation_date <= '12-mar-2017'

and     status_change_date <= '12-mar-2017'

if i do this for all issue_creation week, the end result will look like (forget about the notes column):

week of creation_date   created_issues    open_till_end_of_week      notes

==================  ===========   ======================       =====

5-mar-2017                          2                        1                                   still open =  B

12-mar-2017                        3                        2                                   still open =  C, D

19-mar-2017                        1                        2                                   still open =  D, G

how to translate 'still_open' column into LOD expression ?  notice that issue B was CREATED in week "5-march" and was OPEN. but closed later in Week "12-March".

also, issue C was created on week 12-march and was open, then closed in week 19-march.

i have calculated the end of each week (based on creation date) , but i could not translate this into LOD expression which will into account the status change date

when summing the counter.

Note : i have to show the start date and end date of week based on creation_date.

Thanks

• ###### 1. Re: LOD with Two date columns

Hi Ammar

This:

select  sum(counter) as still_open

from    table a

where   creation_date <= '12-mar-2017'

and     status_change_date <= '12-mar-2017'

is similar to:

SUM(IF  ([Creation Date] <= #12-03-2017# AND [Status Change Date] <= #12-03-2017#) THEN ([Counter]) END)

• ###### 2. Re: LOD with Two date columns

Thanks PRAYSON WILFRED DANIEL for your reply.  Yeah, this is a good formula if i want to run for a specific date. in my case, i want this applied to each week of creation date. the sql command provided is for showing the logic for a certain week.

• ###### 3. Re: LOD with Two date columns

Hi Ammark,

How would you define week of creation? Is it BETWEEN [Creation Date] AND DATEADD('day',7,[Creation Date]) ?

• ###### 4. Re: LOD with Two date columns

In the attached workbook above, i have created two fields from "creation_date" , named "start date" and "end_date".  those have the formula to define a week range.

start date = date(DATETRUNC('week',[Creation Date]))

end date = Date(DATEADD('day',6, DATETRUNC('week',[Creation Date],'Sunday' ) ))

the thing here is that i have to go back and sum the counter for each status change that was done before the end of each week. that would get the open issues count correctly.

Thanks

• ###### 5. Re: LOD with Two date columns

Is this what you are looking for

• ###### 6. Re: LOD with Two date columns

Thanks for the efforts.  the end result i wanted is like this :

week of creation_date           created_issues    open_till_end_of_week

==================    ===========   ================

5-mar-2017                               2                        1

12-mar-2017                             3                        2

19-mar-2017                             1                        2

• ###### 8. Re: LOD with Two date columns

hi Ammar,

I think (big caveat!) it's possible...how do you handle the cases of, C, where it Closes in it's Creation Week, but then is re-opened. It is then re-closed again the following week. The LoD I was thinking would pull the last close date, but if issues are re-opening and closing...it may need something more exotic (...a table calc!)

• ###### 9. Re: LOD with Two date columns

Hi Ammar,

Are you looking for this?

Tableau Workbook 10.1 attached for ref.

Gourav

2 of 2 people found this helpful
• ###### 10. Re: LOD with Two date columns

Thanks for your reply GOURAV SHARMA .  That helped me very much.

• ###### 11. Re: LOD with Two date columns

When i applied this to live data, i got inconsistent results. Attached is the new files of live data.

The result should be:

Start Date  still open    still open issues

=======   ======      ============

10-aug-14       1            iam-65

19-Oct-14        1           iam-443

26-Oct-14        1           iam-443

but i am getting :

still open

=======

0

0

2

I tried playing around with the calculations, but i am not able to get the correct result.  Any ideas please ?

Thank you.