I have a data with dates and Vehicle Number. In some cases vehicle numbers are repeated in a day . So When i need to calculate, i want to know the distinct count of vehicles date wise then i need to sum all the unique counts. For ex on 1st May same vehicle has used twice but should consider 1 time. In 2nd may 2 Different vehicles gone. So total should be 3
Solved! Go to Solution.
Not the prettiest solution but you can add a custom metric with count_distinct(vehicle_number) and set the running calculation as running sum. In the image below you can see distinct count next to the new custom field.
If you're working with a larger data set I suggest you add a column to your table in the underlying data set with sql.
Are you on Looker or Looker Studio?
Its looker Studio
Not sure how large your data is. It might affect what's the better solution. If the data is small enough you can try to go for a table chart. Select the date as your dimension with the date type. And the Vehicle number as the Metric. For the Metric type you click on the pencil icon and select count distinct. Then a little below that in the chart menu click "show summary row".
Thank you for your guidance
In my data for each date single vehicles used for multiple times with other purposes. And vehicle numbers are common in all days. So In "show summary row" its giving the unique count for vehicles in the entire data . But I need total of (1st may unique vehicles+2nd may unique vehicles+ 3rd may unique vehicles ...... ) and there are common vehicles in 1st, 2nd, 3rd....
Not the prettiest solution but you can add a custom metric with count_distinct(vehicle_number) and set the running calculation as running sum. In the image below you can see distinct count next to the new custom field.
If you're working with a larger data set I suggest you add a column to your table in the underlying data set with sql.
Thank you for the suggestion