How to fetch FY columns based on current year

[amitca]

I have a data set with columns FY15, FY16, FY17 and say FY18, now based on time of execution of query i need to fetch current & previous years columns.
For ex. i will run the query today so it should fetch me FY16 & FY17 columns(i.e., table FY16 FY17) as previous & current years data and if i run the query next year it should fetch me FY17 & FY18 (i.e., table FY17 FY18) as previous & current years.

It should be dynamic to fetch data in future for FY19, FY20 and so on.

Thanks

[javiergn · Accepted solution]

Hi, without really knowing how your data looks like it's hard to come up with a final and valid answer but see if the below helps anyway:

your query here
| addinfo
| eval current_year = strftime(if(isNum(info_max_time), info_max_time, now()), "%y"), previous_year = current_year - 1
| eval current_year = "FY" . current_year, previous_year = "FY" . previous_year
| foreach FY* [ eval <<FIELD>> = if("<<FIELD>>" = current_year OR "<<FIELD>>" = previous_year, <<FIELD>>, null()) ]
| table _raw, FY*
| stats values(*) as * by _raw

Addinfo will add information about your search, including the time range you were using.
The next two lines are simply calculating current and previous years based on the info from your time range.
Foreach will basically populate as null() all those FY columns whose name does not match current or previous
And then stats values will get rid of the null columns.

If the above doesn't match your exact needs please post an example of how your data looks like.
I have tested it in my lab with a small subset of data and it seems to do the trick.

Thanks,
J

[amitca]

Thanks it did help, but i need to display only current and previous years data and not other FY years even with empty values.
Ex: Data Set
ID FY15 FY16 FY17 FY18
1 1 0.5 0.2 1
2 1 0.2 2 3
3 2 2 2.5 3
4 3 3 3 2
Based on current year the output should be:
ID FY16 FY17
1 0.5 0.2

2 0.2 2

3 2 2.5

4 3 3

Thanks in advance

[javiergn]

The following works fine for me when importing your sample above as a CSV file (ignore the first line basically):

| inputcsv mycsv2.csv
| addinfo
| eval current_year = strftime(if(isNum(info_max_time), info_max_time, now()), "%y"), previous_year = current_year - 1
| eval current_year = "FY" . current_year, previous_year = "FY" . previous_year
| foreach FY* [ eval <<FIELD>> = if("<<FIELD>>" = current_year OR "<<FIELD>>" = previous_year, <<FIELD>>, null()) ]
| table ID, FY*
| stats values(*) as * by ID

Output:

[amitca]

Yes it did work. Thanks a lot.