Sum latest value multiple field with timechart

[bcouavoux]

Hello !

My data is in this form  :

_time (dd/mm/yyyy), NbRisk, SubProject, GlobalProject

02/05/2021, 10 ,  SubProject1, Project 1
01/05/2021, 4 ,  SubProject2 ,  Project 1
01/05/2021, 5 ,  SubProject3 ,  Project 1
10/04/2021, 80 ,  SubProject1 ,  Project 1
09/04/2021, 32 ,  SubProject2 ,  Project 1
09/04/2021, 12 ,  SubProject3 ,  Project 1

03/05/2021, 11 ,  SubProject1, Project 2
01/05/2021, 40 ,  SubProject2 ,  Project 2
15/04/2021, 60 ,  SubProject3 ,  Project 2
09/04/2021, 2 ,  SubProject3 ,  Project 2
08/04/2021, 4 ,  SubProject2 ,  Project 2
05/04/2021, 3 ,  SubProject1 ,  Project 2

I would like to calculate the last "NbRisk"  by "SubProject"  and sum it by all day and by "GlobalProject" with timechart to see the trend.

For example :

07/05/2021, Project 1, 19
07/05/2021, Project 2, 111
06/05/2021, Project 1, 19
06/05/2021, Project 2, 111
|
|
01/05/2021,Project 1, 89
01/05/2021,Project 2, 103
|
|
10/04/2021, Project 1, 124
10/04/2021, Project 2, 9

I have tried a lot of thing like streamstats/addtotals without success ..

Thanks for ur help !

[kamlesh_vaghela · Accepted solution]

@bcouavoux 

Yes. It is complex. But can you please try this? 

YOUR_SEARCH
| eval GlobalProject= "Prj_".GlobalProject 
| timechart sum(NbRisk) as NbRisk span=1d by GlobalProject 
| eval field="", value="" 
| foreach Prj_* 
    [ eval field=if(field="",if(isnull('<<FIELD>>'),"","<<FIELD>>") ,field.if(isnull('<<FIELD>>'),"",","."<<FIELD>>"))
        ,value=if(value="",if(isnull('<<FIELD>>'),"",'<<FIELD>>') ,value.if(isnull('<<FIELD>>'),"",",".'<<FIELD>>'))
        ] 
| fields field value 
| eval GlobalProject=split(field,","),NbRisk=split(value,",") 
| fields - field value 
| eval tmp=mvzip(GlobalProject,NbRisk)
| mvexpand tmp
| eval GlobalProject=mvindex(split(tmp,","),0),NbRisk=mvindex(split(tmp,","),1)
| fields - tmp

You can try my sample search also to understand the search.

| makeresults 
| eval _raw="time, NbRisk, SubProject, GlobalProject
02/05/2021,10,SubProject1,Project 1
01/05/2021,4,SubProject2 ,Project 1
01/05/2021,5,SubProject3 ,Project 1
10/04/2021,80,SubProject1 ,Project 1
09/04/2021,32,SubProject2 ,Project 1
09/04/2021,12,SubProject3 ,Project 1
03/05/2021,11,SubProject1,Project 2
01/05/2021,40,SubProject2 ,Project 2
15/04/2021,60,SubProject3 ,Project 2
09/04/2021,2,SubProject3 ,Project 2
08/04/2021,4,SubProject2 ,Project 2
05/04/2021,3,SubProject1 ,Project 2
03/05/2021,3,SubProject1,Project 3
01/05/2021,43,SubProject2 ,Project 3
15/04/2021,34,SubProject3 ,Project 3
09/04/2021,21,SubProject3 ,Project 3
08/04/2021,41,SubProject2 ,Project 3
05/04/2021,13,SubProject1 ,Project 3
" 
| multikv forceheader=1 
| eval _time=strptime(time,"%d/%m/%Y") 
| fields NbRisk, SubProject, GlobalProject 
| eval GlobalProject= "Prj_".GlobalProject 
| timechart sum(NbRisk) as NbRisk span=1d by GlobalProject 
| eval field="", value="" 
| foreach Prj_* 
    [ eval field=if(field="",if(isnull('<<FIELD>>'),"","<<FIELD>>") ,field.if(isnull('<<FIELD>>'),"",","."<<FIELD>>"))
        ,value=if(value="",if(isnull('<<FIELD>>'),"",'<<FIELD>>') ,value.if(isnull('<<FIELD>>'),"",",".'<<FIELD>>'))
        ] 
| fields field value 
| eval GlobalProject=split(field,","),NbRisk=split(value,",") 
| fields - field value 
| eval tmp=mvzip(GlobalProject,NbRisk)
| mvexpand tmp
| eval GlobalProject=mvindex(split(tmp,","),0),NbRisk=mvindex(split(tmp,","),1)
| fields - tmp

Thanks

KV

[kamlesh_vaghela]

@bcouavoux 

In your expected OP the time has 07/05/2021,06/05/2021 some are blank ,etc values which are different from dataset. Is there any additional logic also for there?

[bcouavoux]

@kamlesh_vaghela 

I would like to have a timechart by day that corresponding of the addition of the last "NbRisk" value by "SubProject" and summarize by "GlobalProject". 

For example, all the latest "NbRisk" by "SubProject" is additioned and summarize by "GlobalProject" until there is a new value arrived that replace it in the addition. So, based on my example :

07/05/2021, Project 1, 19
07/05/2021, Project 2, 111
06/05/2021, Project 1, 19
06/05/2021, Project 2, 111
05/05/2021, Project 1, 19
05/05/2021, Project 2, 111
04/05/2021, Project 1, 19
04/05/2021, Project 2, 111
03/05/2021, Project 1, 19
03/05/2021, Project 2, 111
02/05/2021, Project 1, 19
02/05/2021, Project 2, 111
01/05/2021,Project 1, 89
01/05/2021,Project 2, 103

The blank in my post is just to say that the dates are continuous but the values ​​are the same.

I dont know if I am clear, it is quite complicated to explain

[kamlesh_vaghela]

@bcouavoux 

Can you please share OP by adding after your YOUR_SEARCH? 

| dedup _time SubProject GlobalProject