4 Replies Latest reply on Nov 21, 2012 4:27 PM by Dean Lizardo

# Calculation to Exclude if Data is Incomplete Over Specified Date Range

Hello,

Couldn't find the exact answer to this question on the forums.

I'm looking for a calculation to exclude an entire set of data if any part of the data is incomplete over my date range. For example,

RegionQ1 2011Q2 2011
Q3 2011
Q4 2011
North525

South

4444
East3167
West
133

In this case, because North and West data are incomplete in 2011, I want to exclude their data from the analysis. In this example, I understand that it is possible to simply filter out North and West. However, my data set has over 1000 rows, and to manually filter which data is complete and incomplete would be too time consuming.

Is there any way to dynamically exclude such incomplete data through a calculation? Also, if I switch my date range, is it possible to have the calculation dynamically refresh based on the date range? If not, I'm fine with manually modifying the calculation based on my dates.

• ###### 1. Re: Calculation to Exclude if Data is Incomplete Over Specified Date Range

Does the missing data have a null value or just doesn't exist? In other words, do you have:

North Q1 2011 6.5

West Q1 2011

West Q2 2011 6.6

or does that second line not exist?

If the latter you could use a filter:

size()=4

with the partitioning set to Date, and then the filter set to true. If you have multiple years, you will need to bring that in as well

1 of 1 people found this helpful
• ###### 2. Re: Calculation to Exclude if Data is Incomplete Over Specified Date Range

Thanks for the quick reply, Alex.

The data does not exist, so it looks like I can use the option you've described.

To confirm my understanding, your filter is looking at the size of the rows in each dataset, and would only return a value if there are four rows, or four values. This means that the filter could be modified so that if data is stored at a monthly level, the filter would be size()=12. And at a daily level, size()=365 or 366. Is this correct?

Also, this is my first time using the "size" function, so could you clarify what it means to have the partitioning set to "Date"?

• ###### 3. Re: Calculation to Exclude if Data is Incomplete Over Specified Date Range

Correct, but we would need to reset it for each year as well if there are multiple years.

Basically, we are saying if we have region and date, look at the number of dates in each region

• ###### 4. Re: Calculation to Exclude if Data is Incomplete Over Specified Date Range

Got it. This does the trick, Alex.

Thank you so much for your help.