Determine the Week of the month in Magic ETL

Randyb

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

PodiumMason

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!

 

 

Randyb

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

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

Randy