13 Replies Latest reply on Dec 14, 2016 10:00 AM by hetal joshi

# Division and Calculated field problem

Hi,

I have two raw data tables with Visitor Information from three countries for three months

RawDataA: Visiting_Month, Transporation_Mode, Visitor_Name, Country_Name

RawDataB: Visiting_Month, Transportaion_Mode, Total_Visitor_Count

Joining fields: Visitng_Month, Transportaion_Mode

I want to create a calculated field showing Visitor Rate for each month.

Formula for Visitor Rate = CountD(Visitors)/Sum(Total_Visitor_Count)   ---- (In excel)

In order to get Visitor Rate I created two Calc. fields with LOD expressions

LOD1: {FIXED [Mode]:COUNTD([Visitor])}

LOD2: { FIXED [Mode] :SUM([Visitor Count])}

In third calculated field using a DIV(LOD1, LOD2)

But it gives me 0. I definitely am doing something wrong here.

Any  guidance would be appreciated.

Workbook and sample chart required attached.

• ###### 1. Re: Division and Calculated field problem

As per the definition for the DIV function, it only returns an integer.

DIV(11,2) will return 5. 11/2 is 5.5

This is why it's only displaying 0, because the result being returned is < 1.

Also, I think your VisitorRate calculation should actually be SUM(Visitor Lod) / SUM(TotalCount_Lod) - you didn't have any aggregation applied previously.

• ###### 2. Re: Division and Calculated field problem

(note to self:  V10.0 workbook)

I don't get your sample chart at all.  The title of the chart says "by country", but there isn't any indication of countries on that chart at all.

What are you really aiming for?

• ###### 3. Re: Division and Calculated field problem

I tried using Float() for the VisitorRate calculation but still ti shows 0. I am not sure, if this would work?

• ###### 4. Re: Division and Calculated field problem

I want to add "Country" in Marks (color) to be able to view separate country visitor count

• ###### 5. Re: Division and Calculated field problem

In the attached I dumped what Tableau is seeing into Excel.  You'll notice that visitor count is duplicated for each individual visitor name, within a given month.

So any SUM([VisitorCount]) is going to be skewed here.

• ###### 6. Re: Division and Calculated field problem

Don't convert anything to float, just do a straight up division. A / B. There's no need to use the DIV function at all.

1 of 1 people found this helpful
• ###### 7. Re: Division and Calculated field problem

@Tom: I am not sure, how do to A/B? Is there a function for that?

• ###### 8. Re: Division and Calculated field problem

@Joe: Thats right, is there a way out with this?

• ###### 9. Re: Division and Calculated field problem

@Tom: I tried the A/B. It worked. Thank you so much! Appreciate taking time to help with this. I have another question related to the above one. Since I need to show two line charts on secondary vertical axis, is there a way I could be able to do that? Dual Axis works just for one Dimension.

• ###### 10. Re: Division and Calculated field problem

If I understand you correctly, you should be able to do this with measure values and measure names.

Remove the items on your rows shelf except for CNTD(Visitor) then drag Measure Values onto the Rows shelf and set those up as a dual axis. Then you can drag measure names onto the color button of the mark card;

1 of 1 people found this helpful
• ###### 11. Re: Division and Calculated field problem

Thank you so very much Tom!! This is exactly what I was looking for. Have been confused for a while how to get to this stage. Appreciate your prompt responses too!

• ###### 12. Re: Division and Calculated field problem

Joshi -- You have the shape you want.  Are you still in need of help getting those duplicate values handled?

• ###### 13. Re: Division and Calculated field problem

@Joe , I still do have duplicates, but took care of it by using MAX(Visitor Count) for each month that eliminates any duplicates. Thank you for following back on the question. Appreciate your time and help. :)