Need calculate a count of each "Su M Tu W Th F Sa" between two dates

[cpm003]

Hello,

i´m looking to get this result between each start /end time.

hope you could help me 😞

For example:

Start time	Endtime	Su	M	Tu	W	Th	F	Sa
2021/07/01	2021/07/17	2	2	2	2	3	3	3
2021/07/05	2021/07/20	2	3	3	2	2	2	2

Thanks in advance

[ITWhisperer · Accepted solution]

| makeresults
| eval _raw="start,end
2021/07/01,2021/07/17
2021/07/05,2021/07/20"
| multikv forceheader=1
| table start end



| eval start=strptime(start,"%Y/%m/%d")
| eval end=strptime(end,"%Y/%m/%d")
| eval days=floor((end-start)/(24*60*60))+1
| fieldformat start=strftime(start,"%Y/%m/%d")
| fieldformat end=strftime(end,"%Y/%m/%d")
| eval days=mvrange(1,days+1)
| eval days=mvmap(days,start+((days-1)*24*60*60))
| eval days=mvmap(days,strftime(days,"%a"))
| streamstats count as row 
| stats count by row start end days
| eval dates=start."!".end
| xyseries dates days count
| eval start=mvindex(split(dates,"!"),0)
| eval end=mvindex(split(dates,"!"),1)
| fields - dates
| table start end Sun Mon Tue Wed Thu Fri Sat

[cpm003]

Hello @ITWhisperer 

Seems to be working nice but i need print additional fields to this result such "Event ID",  "Owner" , "Assigned Group", how could i  get them?

thanks in advance

[ITWhisperer]

You could use this as a subsearch on a join with your original data.

[cpm003]

Thanks for you support, really appreciated

[efika]

Hi @Anonymous 

Try use foreach :

| makeresults 1 
| eval Su=2 
| eval M=2 
| eval Tu=2 
| eval W=2 
| eval Th=2 
| eval F=3 
| eval Sa=3 
| eval total=0 
| foreach Su,M,Tu,W,Th,F,Sa 
    [ eval total=total+<<FIELD>>]