Comparing WTD Sales upto the current time for last 3 week

Prajju
Prajju Member
in Magic ETL

Hi,

 

I want to compare WTD Sales up-to the same point in time for the past 3-4 weeks.

The Period over Period chart only compares WTD sales at the end of the day instead of up-to the current point in time.

How to achieve this in Beast Mode or Dataflow level?

 

Thanks,

Prajwal

Best Answer

  • Unknown
    Unknown
    Answer ✓

    Hi, Prajju,

     

    As with most of these things, there are several ways to do it, and the best option will depend on how you're trying to visualize the data. One way to do it is to create oneBeast Mode calculation that puts each record in a week bucket (e.g., current week, prior week, 2 weeks ago, etc.). That's the easy part.

     

    Then, you'll also need to create a Beast Mode calculation that returns sales amounts for only those datetimes-of-week that are less than or equal to the current datetime-of-week. You can do that using the DayOfWeek(), Time(), and CurrentTimestamp() functions.

     

    Essentially:

    CASE WHEN [Transaction Day of Week] < [Current Day of Week] OR ([Transaction Day of Week] = [Current Date of Week] AND [Transaction Time] <= [Current Time]) THEN [Sales Amount] ELSE 0 END

     

    Hope that points you in the right direction.

     

    Dan

     

Answers

  • Unknown
    Unknown
    Answer ✓

    Hi, Prajju,

     

    As with most of these things, there are several ways to do it, and the best option will depend on how you're trying to visualize the data. One way to do it is to create oneBeast Mode calculation that puts each record in a week bucket (e.g., current week, prior week, 2 weeks ago, etc.). That's the easy part.

     

    Then, you'll also need to create a Beast Mode calculation that returns sales amounts for only those datetimes-of-week that are less than or equal to the current datetime-of-week. You can do that using the DayOfWeek(), Time(), and CurrentTimestamp() functions.

     

    Essentially:

    CASE WHEN [Transaction Day of Week] < [Current Day of Week] OR ([Transaction Day of Week] = [Current Date of Week] AND [Transaction Time] <= [Current Time]) THEN [Sales Amount] ELSE 0 END

     

    Hope that points you in the right direction.

     

    Dan

     

Categories