12 Replies Latest reply on Aug 12, 2016 12:55 PM by Tom W

# Calculation help!

This doesn't seem that it would be that difficult, but I'm having a hard time figuring it out. I have a data source that contains policy information filed through two systems (FSL and CH), which is a dimension in my workbook. Each row of data is associated with either FSL or CH. What I'm trying to do determine if there are any "Insureds" (another field in my data source) that were filed last year in one system (CH) and this year in the other (FSL). I tried creating two sets, one for CH and one for FSL, and then creating a combined set of like members but I can't do it based on a particular field.

Any ideas??

Thanks!

Sheila

• ###### 1. Re: Calculation help!

Hi Pearson,

It's pretty difficult to help without seeing some sample data. Can you please upload a Tableau Packaged Workbook?

Thanks!

• ###### 2. Re: Calculation help!

Tom is right. Hard to make a guess without seeing the data. But I'll make a guess anyway.

IF [FSL or CH Dimension]="CH"

AND [Other Dimension]="Insureds"

AND YEAR([Filing Date])=2015

THEN 1

ELSEIF

[FSL or CH Dimension]="FSL"

AND [Other Dimension]="Insureds"

AND YEAR([Filing Date])=2016

THEN 1

ELSE 0

END

• ###### 3. Re: Calculation help!

Not sure if I did this correctly, but I've attached the workbook.

• ###### 4. Re: Calculation help!

Not sure this one will work because I'm looking for like Insureds filed in one system during 2015 and the other in 2016...I don't have a particular Insured, just looking for if there are any that overlap between the two systems. I attached the workbook to Tom's post. Thanks!

• ###### 5. Re: Calculation help!

When comparing multiple sets across a different dimension (here date) you have to add those sets to Context.

In ur filter shelf use Add to Context

• ###### 6. Re: Calculation help!

Pearson,

The workbook you've attached has like 20 sheets. Can you please let us know which sheet you're working on - what are the names of the fields we need to be looking at here? In situations like this, it's always easier for us if you prepare a trimmed down example of your workbook with a sheet which includes only the dimensions / measures you're referencing in your post.

• ###### 7. Re: Calculation help!

It’s blank, but I was working on the last sheet in the workbook, sheet 46. I need to see the following….

Any “Insured” filed during 2015 (which would be “Transaction Received Date”) that is identified with “CH Florida Only” in the CH/FSL group that was subsequently filed during 2016 identified with FSL in the CH/FSL group.

Just don’t know how to write a formula that would give me that info.

Thanks!

Sheila

Florida Surplus Lines Service Office

800-562-4496 ext 125

• ###### 8. Re: Calculation help!

Which source am I meant to be using? FSL AND CH Data or Sheet 1? The FSL source doesn't work because it's not setup as an extracted source. You would need to extract it and re-upload.

The Sheet 1 source contains no data for 2015 in the Transaction Received Date dimension.

Given the above, can you please re-attach a more contained example with the correct source and the fields in the view? Thanks!

• ###### 9. Re: Calculation help!

ok, let's try this again. I've attached a clean version, with an extract of the 2015-2016 data. Hopefully, this will work.

• ###### 10. Re: Calculation help!

A little tip for future threads, you should limit your extract to a subset of your data or X number of rows. 32MB files will scare some people off. Not me though!

Are you sure you have data in this workbook to suit the scenario you're asking for?

If I drag 'Insured' on to the row shelf and then add this calculation, I don't see any individual 'Insured' that meets the criteria of having a 2015 CH Florida Only and a 2016 FSL.

Here's the calc I used;

IF

(

attr([Ch/Fsl (group)])="CH Florida Only"

)

and

(

attr([Ch/Fsl (group)])="FSL"

)

then 'Meets Criteria'

else ''

end

• ###### 11. Re: Calculation help!

That’s what I’m trying to determine is if there are any in the data. However, how are you “adding” the calculation, just “Add to Sheet”? That doesn’t seem to only show the Insureds in both as I still have everything listed if I drag Insured to Rows.

I have the Transaction Received Date as a filter only selecting 2015 and 2016, and also CH/FSL only selecting FSL and CH Florida Only. I have a copy of Year on Columns with the CH/FSL (group) and then Insured on Rows. The calculation is on color?

Thanks!

Sheila

Florida Surplus Lines Service Office

800-562-4496 ext 125

• ###### 12. Re: Calculation help!

I created the above as a calculated field first, then dragged that into the rows shelf along with the 'Insured' dimension. You can then use it as a filter, but I found that it never returns the 'meets criteria'.

I think you best next step would actually be to break the calculated field down into two parts so you can assess the CH Florida Only as;

IF

attr([Ch/Fsl (group)])="CH Florida Only"

THEN 'CH Florida Only 2015'

ELSE ''

END

and a second formula for the FSL 2016;