I have a requirement where I need to calculate location wise weekly, monthly and total expected revenue for the devices installed. The data is in an Excel lookup as:
Device_Id Location_Id Start_Date End_Date 60000099 20 8/5/2017 10/5/2017 60000091 20 5/5/2017 10/5/2017 60000092 40 8/5/2017 60000093 50 7/5/2017 11/5/2017
In the expected result, I want the data for the time duration to be displayed as:
Date Location Number_of_Valid_Kits 5/5/17 20 1 6/5/17 20 1 7/5/17 20 1 7/5/17 30 1 8/5/17 20 2 8/5/17 30 2 8/5/17 40 2 and so on ...
I can evaluate the validity of kit based on start and end dates but I have no idea about how I can display all the data against each date so that I can search the data from the above format using dates and group it by the location_id
Try like this
your current search giving fields Device_Id, Location_Id, Start_Date, End_Date | eval Date=mvrange(strptime(Start_Date,"%d/%m/%Y"),strptime(End_Date,"%d/%m/%Y")+86400,86400) | mvexpand Date| eval Date=strftime(Date,"%d/%m/%Y") | table Date, Device_Id, Location_Id
You now go a row for each date from StartDate to EndDate, including End_Date and you can do your evaluations.
I think you can just aggregate your devices by start date and location:
<query to return all valid events> ... | stats count(Device_Id) by Start_Date, Location_Id
Hope it helps. Thanks!
Hi Hunters, The Start date won't do as there may be many days when no kit is installed but still many kits stand valid on that day. I missed to keep an empty date in the example but in real scenario, the many kits are deployed once and then few more are added and if any kit has a problem, it is removed