Determine the Week of the month in Magic ETL

Randyb
Randyb Member
in Magic ETL

I'm running a sales forecast dataset and want to be able to determine what week of the month we are currently in so I can calculate the weekly sales forecast target.

Such as if the month target is 100,000 and its week 1 then the weekly target should be 25,000.

I can add a date calculation column that gives me the Week Year for the current day but I can't figure out how I can use the week year without building out an entire Week Year Calendar dataset to pull in which I'd really rather not do.

Let me know if that is my only option or if anyone has any other ideas.

Thanks

Randy

Best Answer

  • PodiumMason
    PodiumMason Contributor
    Answer ✓

    Hey @Randyb,

     

    I don't know that a solution exists in Magic ETL, but I have found a solution that seems to work fairly well in Beast Mode:

     

    FLOOR((DAYOFMONTH(`Date`) - 1) / 7) + 1

     

    I know it's not the solution you asked for, but hopefully it will provide a workaround.

     

    Best of luck!

     

     

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'

Answers

  • PodiumMason
    PodiumMason Contributor
    Answer ✓

    Hey @Randyb,

     

    I don't know that a solution exists in Magic ETL, but I have found a solution that seems to work fairly well in Beast Mode:

     

    FLOOR((DAYOFMONTH(`Date`) - 1) / 7) + 1

     

    I know it's not the solution you asked for, but hopefully it will provide a workaround.

     

    Best of luck!

     

     

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • Randyb
    Randyb Member

    Thanks, I'll do some investigating, I was really hoping to stay away from the Beast Mode approach as this was going to support another calculation that I wanted to keep in the dataset.

    I may have to be willing to compromise a bit as well.

    Appreciate the advise, will advise as to what I end up doing

    Randy

  • Randyb
    Randyb Member

    I ended up using it and it worked fine, gave me a little more flexibility as well, thanks for your help

    Randy

Categories