2 Replies Latest reply on Mar 8, 2016 7:49 AM by Tianyi Zhou

# How to count my data continuously when it is separated into two parts

Currently I have a large amount of data, so large that Excel even can not contain all of them in one spreadsheet.

As a result, I have separate them and put them into two spreadsheets. Please see the attachments.

There is one column called "Email Address Old 1 (or 2)" and I want to count the number of it. Below is the calculation I am using:

IF [Start Date] >= [Mar, 22nd 2015] THEN 0

ELSE

(

IF [End Date] >= [Mar, 22nd 2015] - 3 THEN

COUNT(IF [Selected Date Last Year Old 2] >= [Start Date] THEN [Email Address Old 2] END)

ELSEIF [End Date] < [Mar, 22nd 2015] - 3 THEN

COUNT(IF [Selected Date Last Year Old 2] >= [Start Date] AND [Selected Date Last Year Old 2] <= [End Date] THEN [Email Address Old 2] END)

END

)

END

In this query, [Start Date], [Mar, 22nd, 2015] and [End Date]  are parameters. [Email Address Old 2] is a column in the second spreadsheeet. A date selection is necessary.

[Selected Date Last Year Old 2] is a calculation whose query is shown below:

IF [Created Old 2] >=

(

IF [Start Date] >= [Feb, 29th 2016] THEN [Start Date] - 366

ELSE [Start Date] - 365 END

)

AND

[Created Old 2] <=

(

IF [End Date] >= [Feb, 29th 2016] THEN [End Date] - 364

ELSE [End Date] - 365 END

)

THEN

[Created Old 2]

END

In this query, [Feb, 29th 2016], [Start Date] and [End Date] are parameters and [Created Old 2] is a column.

For some reasons, I need to compare the data this year and last year so I have to pick [Start Date] and [End Date] in this year (you might know parameters are always fixed).

In my query, you will see no matter how you choose [Start Date] and [End Date], the date you see last year will always start in the same DATE and end in the same DAY (I mean Mon, Tue .....)

I need to do this on purpose.As a result, I can compare the data in two TIME LINE perfectly.

Also, the best way I can figure out is to put two calculations into two data source. Below is the explanation:

First, create a calculation(let's call it "CA") ONLY counting the data from Jan, 5th to Mar, 21st. If the [End Date] stops in or before Mar, 21st, I will directly put a conditional sentence inside a COUNT(). If the [Start Date] is behind Mar, 21st, then I will make this calculation 0.

If [End Date] stops after Mar, 21st, then I will find the total count from the start date to Mar, 21st first and then add this result to the second calculation, which count the data from Mar, 22nd to Apr, 30th.

Same idea, the second calculation (let's call it "CB") will only be responsible for the counting from Mar, 22nd to Apr, 30th. In this case, if the [End Date] is before Mar, 22nd, I will make this calculation 0. If [Start Date] is after Mar, 22nd, then I can directly count.

If [Start Date] is before Mar, 22nd, then I will find the result of the counting from Mar, 22nd to the [End Date] first.

You guys may know, [Start Date] and [End Date] will be applied to all the calculations. Finally, I will add up CA & CB.

Now my problem is, the query above is for CA and it always gives me 0.....

Hopefully everybody can understand my difficulties. If not, allow me to explain to me in the reply.

Can anybody help me with this problem? If my problem results from something wrong inside the query above, please let me know.

Or, if you have an better idea, please tell me.

Any tips will be appreciated. Thank you all in advance.

• ###### 1. Re: How to count my data continuously when it is separated into two parts

Hi Tianyi-

Is your real data source Excel files?  I assuming these are just samples as they are fairly small.

If you are using Excel files, you could import them both into access and connect as a single data source.

You could do a custom SQL connection if you are on a PC and do a union on the two files.

Defining Custom Joins with the Custom SQL Option | Tableau Software

Version 9.3 has a union connection built in.  It is still in the final beta, but is expected to be released soon.

I think if you get those files combined it will solve a bunch of your issues.

• ###### 2. Re: How to count my data continuously when it is separated into two parts

I am sorry to tell you I am not allowed do any edition with the company's data source.As a result, I have to blend those Excel files into Tableau.

I do agree with you. The size of the Excel files is quite small. However, the amount of the data still exceeds the MAX number of column of Excel so I have to split it into two parts.

Now I need to play around those queries and finally failed to do that.

Thank you again. I appreciate your help.