'How can I find Percentage using difference between 2 DateTime counts?
I am looking for a way to take the number of [Closed Time] records as a total count and subtract from the number of [Open Time] records as a total count to be able to find the percentage of closed tickets.
To explain a little more, every ticket has a [Open Time] that is in a DateTime format. When a ticket is closed, a [Closed Time] is entered in the data source in a DateTime format. So I am looking to find out the percentage of tickets that were opened that are now closed.
Tried to use
SUM(COUNT_DISTINCT(OPEN TIME) - COUNT_DISTINCT(CLOSED TIME))
to create the difference but get an error that "Re-aggregating metrics is not supported." and using something like Date_Diff only give me the time between the 2 dimensions on a per ticket basis but not a total count of records so that i can convert into a percentage.
Data Studio Dimensions:
Solution 1:[1]
This was resolved by using the following calculated field setup for the data source:
COUNT_DISTINCT(Closed time)/COUNT_DISTINCT(Open Time).
This can then be changed from a number to a percent using the edit ability of Data Studio.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | ouflak |