Hi all,
Here is my trouble. I compile a list of receipt that contains 4 type of information I'm interested in : Date + Product + Amount + Price.
In this process I discovered that I had some information missing and the periodicity of the receipt differs.
Sometimes I get a Quantity without a Price, sometimes I miss the whole receipt (never received).
On top of that, I might receive 2 receipt for the same product on the same month.
My purpose (yours too if you can help) is to determine how much information I am missing. I am drawing charts with these informations and I want to present an indication of the accuracy of the rendering.
It's easy to figure out the accuracy with the set of data I have provided as an attachment.
But I want an automated solution. Since I have much more products to add to the list.
I must note that frequency of receipt can vary. For example, receipt can suddenly be sent monthly for water if I change my provider. So I'll have series with receipt sent every month and every two month.
I have set up a pivot table and segment to easily display the information.
Goal is to determine how much data is missing in each serie since the first record and today. Expected results are :- Quantity Water : 2 (July & October 2020).
- Quantity Gas : 1 (February 2020)
- Price Water : 5 (April, July & October 2019 + January, July & October 2020)
- Price Gas : 1 (February 2020)
What does not work :
- counting the number of dates in one serie to figure out frequency. It will not show the receipts I haven't received (or lost).
- counting the number of value of either serie of product. Sometimes I get 2 values the same month which ruins this idea.
Whatever works, I'll use it. I've been struggling with that one for weeks now. Time to admit I need help.
Thx.
Bookmarks