topic Represent only last 3 days of a date field in Integration, Extension & APIs

Represent only last 3 days of a date field

atudela — Mon, 30 May 2022 11:49:41 GMT

Hello!

I have a datefield that corresponds to Shipping Date and I would like to represent a graph to know how many items where shipped on the last 3 days. These last 3 days do not necessarily have to be consecutive days, as there may be days in which nothing was shipped. I would like to represent the last 3 days in which shipping occured.

I would like to do this on the script but I am not sure how to do it.

Any ideas? Thanks!

Re: Represent only last 3 days of a date field

saranyadurai — Mon, 30 May 2022 12:58:18 GMT

Hi @atudela ,

Can you try this,

1. Load your main Table,

2. Find the Max date from the table like,

Left Join (TableName)

Load KeyField,

Max(ShippingDate) as MaxDate

Resident TableName group by KeyField;

3. Create a flag ,

Load *,
If(MaxDate-ShippingDate<=2,'Last3daysSales',null()) as Last3daysSalesFlag
Resident Test;

4. Front end Expression

Sum({<Last3daysSalesFlag={'Last3daysSales'}>}Value)

I hope, it will help you

Thanks

Re: Represent only last 3 days of a date field

atudela — Tue, 31 May 2022 08:22:03 GMT

Thanks for your answer! I was not sure if your propasal was going to work because I was not sure if the following condition If(MaxDate-ShippingDate<=2 ... was going to give me as a result the last day in which shipping occured and the two previos days to this date;

eg if Max date=12-05-2022, the other two dates obtainedd would be: 11-05-2022 and 10-05-22.

However, I wanted to obtain the last 3 days in which shipping occured, this is if no shipping occured on the 10-05-2022 I would like the 3 dates to be displayed to be: 12-05-2022, 11-05-2022 and 09-05-2022 (considering that shipping occured on day 9).

Maybe your proposal did manage to do this but I did not understand it.

In the end I decided to solve it in a different way.

In my main table I created the field:

Date(max(ShippingDate,3),'DD-MM-YYYY') as Shippingload,

afterwards I did a resident load of the main table as follows:

RESIDENT MainTable where ShippingDate>=Shippingload

This works fine.