Hi all,
I have a problem that I cannot solve.
I have data that is a result of a loadjob where the fields are named 0_PREVIOUS_MONTH, 1_PREVIOUS_MONTH, 2_PREVIOUS_MONTH, ..... 12_PREVIOUS_MONTH.
I would like to add the values of the fields starting from 1/4 up to the current month ..
Let me explain with an example:
today we are in November, I need the sum of a line that starts from April 1st until today.
So if I do the current month -4 + 1 = 8, I have to add:
4_PREVIOUS_MONTH + 5_PREVIOUS_MONTH + .... + 11_PREVIOUS_MONTH which is exactly 8 months.
I thought of a foreach with this syntax:
| foreach * _PREVIOUS_MONTH [eval TOTAL = TOTAL + if (* _ PREVIOUS_MONTH> = 4, <<FIELD>>, 0)]
but it does not work.
You can help me? I'm going crazy to find a solution 🙂
Tks
Bye
Antonio
You still need to initialise TOTAL (otherwise it remains null)
| loadjob savedsearch="antonio:enterprise:20211025_PASSAGGIO_AGGREGATO_DATE"
|where TIPOLOGIA="fs_ampliamenti_ip" AND OFFERTA="DIRETTA" AND NOT like(STATO,"KO%")
| eval TOTAL=0
| foreach *_PREVIOUS_MONTH [|eval TOTAL = if (<<MATCHSTR>> < ANNOFISCALE, TOTAL + '<<FIELD>>', TOTAL)]
|table TOTAL
0_previous_month is November, 1_previous_month is October, 7_previous_month is April so you might want to try something like this
| foreach * _PREVIOUS_MONTH [eval TOTAL = if (<<MATCHSTR>> < 8, TOTAL +'<<FIELD>>', TOTAL)]
Hi ITWishperer,
Thank you for your answer
Does not work 😞
it does not return anything.
| eval TOTAL = TOTAL + '<<FIELD>>' does not work.
Try initialising TOTAL prior to the foreach
| eval TOTAL=0
in this case it returns me TOTAL = 0
Please share your current search query
My query:
| loadjob savedsearch="antonio:enterprise:20211025_PASSAGGIO_AGGREGATO_DATE"
|where TIPOLOGIA="fs_ampliamenti_ip" AND OFFERTA="DIRETTA" AND NOT like(STATO,"KO%")
| foreach *_PREVIOUS_MONTH [|eval TOTAL = if (<<MATCHSTR>> < ANNOFISCALE, TOTAL + '<<FIELD>>', TOTAL)]
|table TOTAL
the result of the loadjob :
obviously I have translated *_PREVIOUS_MONTH in "*_MESE_PRECEDENTE" 🙂
You still need to initialise TOTAL (otherwise it remains null)
| loadjob savedsearch="antonio:enterprise:20211025_PASSAGGIO_AGGREGATO_DATE"
|where TIPOLOGIA="fs_ampliamenti_ip" AND OFFERTA="DIRETTA" AND NOT like(STATO,"KO%")
| eval TOTAL=0
| foreach *_PREVIOUS_MONTH [|eval TOTAL = if (<<MATCHSTR>> < ANNOFISCALE, TOTAL + '<<FIELD>>', TOTAL)]
|table TOTAL
I had initialized TOTAL but a few lines higher.
While now it works.
I also inserted these lines:
| eval CURRENT MONTH = strftime (relative_time (now (), "-0d @ d"), "% m")
| eval YEARLY = CURRENT MONTH + 1-4
and changed the foreach:
| foreach * _PREVIOUS_MONTH [| eval TOTAL = if (<<MATCHSTR>> <ANNOFISCALE, TOTAL + '<<FIELD>>', TOTAL)]
You are a Great !!!
Thank you so much