Formula to Show the Sum of Weekly Quantity in a Transaction

Scenario

The formula shows the sum of weekly quantity used in a transaction.

Solution

1. Go to Lists > Search > Saved Searches > New
2. Select Transaction
3. Under Results tab, add the below formulas:

• Field: Formula (Numeric) | Summary type: SUM | Formula: CASE WHEN {trandate} BETWEEN {today}-(to_number(to_char({today}, 'D'))) AND {today} THEN {quantity} ELSE 0 END | Summary label: This Week
• Field: Formula (Numeric) | Summary type: SUM | Formula:CASE WHEN {trandate} BETWEEN {today}-(to_number(to_char({today}, 'D'))+7) AND {today}-(to_number(to_char({today}, 'D'))) THEN {quantity} ELSE 0 END | Summary label: Last Week
• Field: Formula (Numeric) | Summary type: SUM | Formula:CASE WHEN {trandate} BETWEEN {today}-(to_number(to_char({today}, 'D'))+14) AND {today}-(to_number(to_char({today}, 'D'))+7) THEN {quantity} ELSE 0 END | Summary label: Two Weeks Ago
• Field: Formula (Numeric) | Summary type: SUM | Formula:CASE WHEN {trandate} BETWEEN {today}-(to_number(to_char({today}, 'D'))+21) AND {today}-(to_number(to_char({today}, 'D'))+14) THEN {quantity} ELSE 0 END | Summary label: Three Weeks Ago

Note: To do the other formula if the user wants to see the per week for the whole year, you may just revise the formula by changing the numbers below

 

CASE WHEN {trandate} BETWEEN {today}-(to_number(to_char({today}, 'D'))+21) AND {today}-(to_number(to_char({today}, 'D'))+14) THEN {quantity} ELSE 0 END

The above formula shows the quantity three weeks ago. 21 (7x3) was computed by multiplying 7 days per week and 3 weeks. 14 (7x2) was computed by multiplying 7 days per week and 2 weeks which is the previous week.

4. Put search title
5. Click Save & Run