summary table with continuous count of status duplicates
I have a quotelog table that shows different status for each quote. Sometimes this status can be repeated for each quote. in tbe below image i filtered just one "quote" to demonstrate this. I want to be able to see for each "quote" if it had more than 1 count of a status. Im not sure how this would be designed. column or a measure. but simply put in the below image i want a summary that says
Quote Number of times status 2 occured Number of times status 6 occured
84848 2 2
The key thing is that i need this to be unaffected by my date filter in the report. its also imporant that i know which status each count is for.
That works, however i need to use this as a filter. Here is what each status number means:
I want to be able to on a daily basis track the numbers of quotes where it had status 7 (broker gets estimate). but the issue i run into is that this status 7 can appear multiple times and i only want it to count once ( the first time in which it occured). You might suggest that i should remove my duplicate statuses for each quote and only keep the first instance. This isnt the best solution because if you notice in my initial post, status 2 ( assigned to broker) also occured twice, if i were to only keep the first instance, it would be assigned to the wrong person.
The main question becomes: How can i keep the duplicate statuses of each quote and still be able to count for each quote a distinct count of each status.
so on any given day i want to filter my report and count for each broker how many quotes went through "status 7" on that given day. Say, today a broker took a quote to status 7 ( i only want to count this status once for each quote) BUT he took the same quote to "status 7" 5 days ago, then i dont want it to count for today, because it would have been counted 5 days ago and is now simply a duplicate
perhaps what i need is a version of this table which shows only the last instance of each quote's status (which i have already done) and use the main table that has the duplicate statuses as a filter where the count of each status occurence for each quote is NOT greater than 1