foreach problem, sum of several fields conditioned to the name of the fields

[antonio147]

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

[ITWhisperer]

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)]

[antonio147]

Hi ITWishperer,

Thank you for your answer

Does not work 😞
it does not return anything.

| eval TOTAL = TOTAL + '<<FIELD>>' does not work.

[ITWhisperer]

Try initialising TOTAL prior to the foreach

| eval TOTAL=0

[antonio147]

in this case it returns me TOTAL = 0

[ITWhisperer]

Please share your current search query

[antonio147]

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 :

[antonio147]

obviously I have translated *_PREVIOUS_MONTH in  "*_MESE_PRECEDENTE" 🙂

[ITWhisperer · Accepted solution]

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

[antonio147]

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