Hi,
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
Thanks,
Zubair
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 Start_Date to End_Date, including End_Date and you can do your evaluations.
Dear Zubair,
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!
Hunter
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