Hello,
I'm trying to create a customers gained and lost report using a pivot table (see attached Excel file).
In the pivot table, "Customer" is a row field, "Date" is a column field, and a "Count" of records is a value field. The values field is calculated to "Show Values As" a "Difference From" the previous date. This shows which customers were added or removed each period. However, I want to filter this list to only show customers that were added or removed (assuming thousands of rows of customers). But when I try to add a "Values Filter" to the "Customer" field and choose items that are not equal to zero in the "Count" field, it does not work. It only works when there is no calculation on the count field. It seems that when there is a calculation on the count field (difference from previous date), the values filter does not work.
Any thoughts on how to create a values filter on a calculated field?
Thanks,
David
Hi David,
Please upload your file again as it didn't upload properly and I can't download it. Please be sure to wait for the green text below the 'Start Now' button to tell you the upload has successfully completed.
Thanks,
Mynda
Alright, here's the file again. I got the "Upload completed successfully" message; let me know if it doesn't go through.
David
Hi David
You could try adding a helper column and then filter it.
Refer attachment.
Hope this helps.
Sunny
Hi Sunny,
Yes, a helper column would be a really good workaround as long as the number of periods being compared is static, which in this example is 3 (2/28, 3/31, and 4/30). Hence, count of 3 is filtered out from the pivot table to exclude customers that weren't present in all three periods.
However, this issue is I have over 25 million rows of data and, more importantly, need to dynamically change which periods I show changes in. For instance, in various scenarios, I may need to show changes between 1/1/21 and 12/31/21, show changes every quarter, show changes from the 1st day of the month to the 7th and the 15th, etc. I think the pivot table elegantly solves the problem of calculating those changes dynamically. The only remaining issue is just to filter the massive dataset to show customers that haven't changed in the dynamically selected period or periods.
David
