1 Reply Latest reply on Apr 27, 2015 9:41 AM by Jonathan Drummey

Adding static number to number of records based on specific criteria

Hello,

I need help adding a number to the total number of records based on specific criteria.

We count access to dynamic content (the column named Number of Records in screenshot) and access to static content (the column named Always on counts for FY15Q2) and this info is maintained in two different data sources.

I initially tried to create a calculation so Tableau would count the records in the static content data source and add it to the count of records in the dynamic content but that didn’t work so I thought just try adding the number to the number of records.

Well, that's not working either.

I tried to add a specific number (19) to the number of records (4) based on specific criteria, which should give me a total of 23.

I created a calculated field named “FY15 Q2 Security Always on Total” and just added the number 19 to the formula.

I then have another calculation (All Totals) that totals the counts for the static and dynamic data.

Tableau multiplies the number of records (4) times 19 for a total of 76 for my Always on counts for FY15Q2.

Can you tell me what I’m doing wrong and/or point me to where I can find info to help me with this?

• 1. Re: Adding static number to number of records based on specific criteria

There are a couple of different things going on here.

First, explaining the 76 vs. 19 problem. There are three levels of calculation in Tableau: record-level, aggregate, and table calculation. You've defined the Always on counts as record-level measure, so it returns 19 for each record. There are 4 records for SEC/15-02, so SUM(Always on counts...) = 19+19+19+19 = 76. You could use something like MIN, MAX, AVG, or ATTR as the aggregation to end up with just 19.

Second, blending happens *after* aggregation, not before (like a left-join would). If the blend has both Fiscal Quarter and Primary Architecture as linking dimensions, nothing is returned because there is no "Security" value for Genericarchitecture in the primary data source.

If you turn off Primary Architecture as a linking dimension, then this appears:

The reason why you get a 19 for each is that SUM(Number of Records) from the secondary is performed for each value of Genericarchitecture, since the blend is on only Fquarter.

I'm guessing that maybe the SEC in the primary and Security in the secondary are corresponding values. In that case you could set up a calculated record-level field to use to blend on instead, or you could change the alias for one of them to be the other since Tableau aliases are also evaluated prior to the blend.

Jonathan