Datameer function
I have column A and column B having dates scattered in last 6 months.currently I am facing the situation of finding missing date entries with in last 6 months and the date value is repetitive for different values in the column A. Do we have some standard function in datameer?
-
I'm not certain that I understand the scenario fully, but I think the following is helpful. If it seems out of place, please clarify your scenario with a mini example of data you have and the expected results from it.
I would start by using the EXPAND_DATE_RANGE function to get a complete list of all the days in the range you're interested in. I'd do this step in a separate sheet from your original data.
Next, I'd use the Join function to join your original data to the results of EXPAND_DATE_RANGE to see which dates match the results. For this step, I would perform a Left Outer Join using the EXPAND_DATE_RANGE data on the left and your original data on the right.
Then, I'd take the results of the Joined sheet and add a column that has a value of 1 if a date is populated in the joined sheet. If the date is empty, make the value 0 or null.
Lastly, create a grouping sheet with the EXPAND_DATE_RANGE dates as the first group and then use the GROUPSUM function to add up the column of 1's and 0's. This will give you the counts of records populated in your groups.
Hope this helps!
Please sign in to leave a comment.
Comments
1 comment