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

    Tianyi Zhou

      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.