Help with a calculated field using previous dates!?

Q&A
, , ,
1

Hiii!! I’m creating a pivot table showing the sales each month but only of new products so I need to check if I had sales on the month of the activation date. If I had sales then is not a new product so it should show 0 on every month. If its a new product then it should show the values.

image
image793×269 5.13 KB

I’m trying this:

ifelse(truncDate(‘MM’, {activation_date}) = truncDate(‘MM’, {sales_date}) AND isNull({sales}), 0,{sales})

But I know it’s not right cause i’m not focusing on the sales of the first month. Any ideas?? :smiley:

2

Hello @ramoshe, I think you are really close! My initial thought to resolve this will require a few different functions.

This will create a fairly complex ifelse statement at the end, but my thought is we will need to figure out how many months away from the Activation Date each month value is, pull in the sales value for the activation date month on each row using a dynamic lag function, and check on every date month column if that number is greater than 0.

Create a field for the Sales Date in Months:
sales_date_month = truncDate(‘MM’, {sales_date})

Create a field to get the number of months from activation month:
monthsFromActivation = dateDiff(truncDate('MM', {activation_date}), {sales_date_month}, "MM")

Now we will write a lag function to pull in the sales for the activation_date month:

activationDateSales = lag
(
     sum({sales}), 
     [{sales_date_month} DESC], 
     {monthsFromActivation}, 
     [{product}, {sales_date_month}]
)

Here is where there might be an issue. There is a chance that lag function will create a syntax error, if that happens we may be able to find a workaround, but it might also mean it is time to add a “New Product” tag into the dataset custom SQL when you ingest it. Also, you may need to switch [{sales_date_month} DESC] to ASC but I think DESC is correct. I’m also not sure if this partition [{product}, {sales_date_month}] is necessary but it might be.

Now craft the ifelse:

newProductSales = ifelse(
{sales_date_month} > truncDate('MM', {activation_date}) AND {activationDateSales} > 0, 0,
{sales_date_month} > truncDate('MM', {activation_date}) AND {activationDateSales} = 0, {sales},
{sales_date_month} = truncDate('MM', {activation_date}) AND {sales} > 0, 0,
isNULL({sales}), 0,
0
)

newProductSales should give you the result, or get you close to what you are hoping to achieve. I will mark this as the solution, but if you have questions about any errors, let me know!

3

Woow thank you so much for your help. However I get the error: Expression {{argumentName}} for function {{functionName}} has incorrect argument type {{incorrectArgumentType}}. Function syntax expects {{functionSignature}} when trying to create the lag function. Any idea why?

4

Hello @ramoshe, I am thinking the error is likely coming from the {monthsFromActivation} and/or the partition field [{product}, {sales_date_month}]. To test this out we can try a few debugging techniques. I would recommend bringing that field into a table with some of the other necessary values to test it outside of the ifelse statement.

Check what values you are receiving from the dateDiff field. We will want that number to be a positive number so that it works properly in the lag function, so we may need to switch the order of the fields here if that is the case. You can also try adding a generic number value like 1, 3, 5, etc. into that field to see if the problem is resolved, which may mean we need to handle that differently. You can also try removing that partition field altogether, and try it just with {product} and exclude {sales_date_month}.

Let me know what you find out testing this, and we can likely resolve the errors.

5

Hello @ramoshe, I will mark my above response as a solution. If you have any follow up questions or if it is working as expected, please let me know. Thank you!