Sum up number of products ordered over a specific periods

Question & Answer
, , , , ,
1

Hi,

I need to build up a report, where for every individual product number a sum of “products ordered” is created for the 90 days from the articles has gone online (field “online since”).

Every product has an individual date in “online since” - means the formula needs to look up for the “online since” date and make a sum of all products from this date to the date “online since” + 89 days ( incl. online since date = 90 days).

Examples:

SKU 123456: Online since 01.01.2023 => sum of products ordered for period 01.01.2023 to 31.03.2023

SKU 987654: Online since 01.05.2024 => sum of products ordered for period 01.05.2024 to 29.07.2024

How can I do this?

Thanks in advance for your help!

2

Hi @Thomas_F and welcome to the QuickSight community!
Do you have another date field that provides the sale date of a product?
If so, you could do with something like the following:

SumOver(
ifelse(
{OrderDate} >= {OnlineSince} and {OrderDate} <= addDateTime(89, ‘DD’, {OnlineSince}),
{OrderedQuantity},
NULL), PRE_AGG)

For your scenario, you’ll need to switch out for your actual field names. Let me know if this works for your case or if you have any additional questions!

1 Like
3

Hi @Brett,

many thanks for your answer and formular.

I was on vacatoin last week, but I justed tested it with the correct field names.:

sumOver(
ifelse(
{Date} >= {Online since} AND {Date} <= addDateTime(89, ‘DD’, {Online since}),
{# Products ordered (all pms)},
NULL), PRE_AGG)

I get this error message:

Expression {{argumentName}} for function {{functionName}} has incorrect argument type {{incorrectArgumentType}}. Function syntax expects {{functionSignature}}.

Honestly, I do not understand the meaning of the error message, but I found out that by removing the “sumOver” it seems to work :slight_smile:

ifelse(
{Date} >= {Online since} AND {Date} <= addDateTime(89, ‘DD’, {Online since}),
{# Products ordered (all pms)},
NULL)

Many thanks for your help and best regards,
Thomas

1 Like
4

Glad you were able to get this working!