Cumulative last 12 months KPI with Date

[Hemambica444]

Hi Experts,

I need your help on the below scenario.

Need to calculate a flag , based on 4 fields given in Table A.

Output will be BookingNumber and Flag (Table B). (No other columns required)

Flag Rule:

At the date of booking, check the 12 month sales of corresponding customer(irrespective of bookings).

If BookingSales=Rolling 12 CRSales (new column),Y else N

1 customer can do multiple bookings on same or different dates.

Can anybody help? Thanks.

ASIS (Table A)

ToBe Expected Output (Table B)

  

Logic for Bk1:

CR1 Sales from 20-04-2020 to 20-04-2021 = 10

BookingSales=Rolling12CRSales

Bk1 Flagged as 'Y'.

Logic for Bk2:

CR2 Sales from 20-03-2020 to 20-03-2021 = 20+20=40

BookingSales<>Rolling12CRSales

Bk1 Flagged as 'N'.

=============

I tried set analysis it didnt work.

sum(aggr(Sum({<BookingDate={">=$(=Date(Max({<BookingNumber=p(BookingNumber)>}BookingDate),-365))<=$(=Date(Max({<BookingNumber=p(BookingNumber)>}BookingDate)))"}>}BookingSales),BookingCustomer,BookingNumber))

I thought if the KPI logic is derived at backend , it will be helpful.

Only BookingNumber and Flag will be in the final table.

Can anybody help? Thanks.

Inline Data:

Bookings:
LOAD * INLINE [
BookingNumber, BookingCustomer, BookingDate, BookingSales
Bk1, CR1, 20-04-2021, 10
Bk2, CR2, 20-03-2021, 20
Bk3, CR1, 20-01-2020, 15
Bk4, CR2, 20-08-2020, 20
Bk5, CR3, 16-02-2020, 15
Bk6, CR3, 15-03-2021, 5
];

[MayilVahanan · Accepted solution]

HI @Hemambica444 

Might be, attachment will help

[Hemambica444]

Hi MayilVahanan,

it’s working as expected.

I will apply this logic to my version .

Thank you so much