4 Replies Latest reply on Nov 8, 2018 8:15 AM by conacher

# Help defining LOD or calculated field?

Hello,

I have attached a 2018.3 packaged workbook.  My problem is with the first sheet (image below).

The first circled column (September 2018 - Award Date field) and second circled column (October 2018 - Award Date field) have the correct calculations for what I need.  Open Claim Month field = Adjusting Loc Recd Date in the underlying Data->View Data file.   I need only those two columns to display so it looks like this:

September 2018                 October 2018

3rd Party C&R                                                                                   1

C&R                                                           5                                     8

Findings and Award                                                                          1

Stipulated Award                                       4                                      2

Grand Total                                                9                                    12

The counts for the months should count the number of distinct claim numbers that have an Open Claim Month (Adjusting Loc Recd Date) within September 2018 (and then October 2018) and also have have an Award Date within September 2018(and then October 2018).

I think it might be something like a Level of Detail using include/exclude but I can't make it work.  I also think the solution might be obvious to someone with more a bit more knowledge of Tableau than I have.

Any and all help appreciated.  Thank you.

~S

• ###### 1. Re: Help defining LOD or calculated field?

Hi conacher

Please find the attached workbook for the solution.

Please see that I created a new calculation called "Claim number new".

Thank you

Srinath

• ###### 2. Re: Help defining LOD or calculated field?

Hi S,

I figured it out based on the table that you put in your question.  Thanks for including so much detail, it really helps!

The first part is to create a calculated field - I called it "Column Filter".  You want to look for values where the month and year of the open claim month and award date are the same and only keep those.  Here is the calculation:

if datetrunc('year',[Award Date]) = datetrunc('year',[Open Claim Month])

and datetrunc('month',[Award Date]) = [Open Claim Month]

then "Include" else "Exclude" end

Once you have this field created, drop it onto your Filters pane and only select "Include" (make sure "Exclude" is not selected).  That will remove the values from the second September column.  Now, if you remove the MY(Award Date) field from the Columns shelf in your viz, it'll show September and October, and only the number of claims that were both opened and awarded in that same month.

Let me know if you have any questions.

Best,

Paul

• ###### 3. Re: Help defining LOD or calculated field?

Hi S - if my answer worked for you can you please mark it as the correct answer so that others are able to search for this answer as well?  Thanks!

• ###### 4. Re: Help defining LOD or calculated field?

Paul,

My office system browsers do not display the "Correct Answer" button so I will when I'm in front of my home computer again.  Thank you again.

~Sharie