How do I get future data being populated to current year?

yogesh_1
yogesh_1 Member
in Magic ETL

I have a requirement to show Next fiscal Year data into Current chosen fiscal year. So if user choses , FY 23, it should show data for Fy24 in 1 column.

Please see below: If FY is selected, then monthly data is shown for current fiscal year.

and similarly NextFy data (Fy+1) is also displayed like below. Please suggest.

Oct-22

Nov-22

Dec-22

FY

100

200

445

NextFY

150

234

221

Tagged:

Best Answer

  • michiko
    michiko Contributor
    Answer ✓
    1. Supposed your data is stored as below
    image.png

    2) create a beast mode called Offset Date

    case
    when FY = 'FY' then Date
    when FY = 'FY+1' then DATE_SUB(Date, INTERVAL 1 year)
    end

    3) Construct a crosstab like below.

    image.png

    4) You will need to tweak the beast mode a little bit more if you want to be able to select other FY values

Answers

  • michiko
    michiko Contributor
    Answer ✓
    1. Supposed your data is stored as below
    image.png

    2) create a beast mode called Offset Date

    case
    when FY = 'FY' then Date
    when FY = 'FY+1' then DATE_SUB(Date, INTERVAL 1 year)
    end

    3) Construct a crosstab like below.

    image.png

    4) You will need to tweak the beast mode a little bit more if you want to be able to select other FY values

  • yogesh_1
    yogesh_1 Member

    @michiko Yes, the flexibility is given to users to choose the required fiscal year.

    Also can you explain this:

    case
    when FY = 'FY' then Date
    when FY = 'FY+1' then DATE_SUB(Date, INTERVAL 1 year)
    end

  • michiko
    michiko Contributor
    edited January 2024

    The beast mode creates the respective offset date so that the date align back to FY

    image.png

Categories