Forum Discussion

aJ2's avatar
aJ2
Icon for Helper I rankHelper I
8 years ago
Solved

Flag against orders which were placed when a code was run

Hi.

 

I have a table of orders where the revenue, coupon used, validity period etc is mentioned. There are some instances wherein the on the days coupons were run, in some orders no coupon was used.

 

For example,

dateorder idtotal valuecoupon codecoupon valid fromcoupon valid till
23-Dec-171001100MC1023-Dec-1724-Dec-17
23-Dec-17100232MC1023-Dec-1724-Dec-17
23-Dec-171003342   
23-Dec-1710043534MC1023-Dec-1724-Dec-17
23-Dec-171005596MC1023-Dec-1724-Dec-17
23-Dec-17100650   
24-Dec-17100743   
24-Dec-171008564MC1023-Dec-1724-Dec-17
24-Dec-17100931   
24-Dec-1710103265MC1023-Dec-1724-Dec-17
24-Dec-1710119956   
24-Dec-171012213MC1023-Dec-1724-Dec-17
24-Dec-171013635MC1023-Dec-1724-Dec-17
25-Dec-17101445   
25-Dec-171015646   
25-Dec-171016458   
25-Dec-17101798   

 

So I want a new column to identify if a coupon code has been used on that day. So against order id 1009, I want True against it as the order has been placed during the time a coupon code is running.

 

Kindly guide

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    8 years ago

    aJ2,

    Please create the following columns in your table.

    Index = RANKX(FILTER(Table1,Table1[order id]<EARLIER(Table1[order id])),Table1[order id],,ASC)
    prefromdate = 
VAR LastNonBlankIndex =
    CALCULATE (
        LASTNONBLANK ( Table1[Index], 1 ),
        FILTER (
            ALL ( Table1 ),
           Table1[Index] <= EARLIER ( Table1[Index] )
                && NOT ( ISBLANK ( Table1[coupon valid from]) )
        )
    )
RETURN
    CALCULATE (
        MAX(Table1[coupon valid from]),
        FILTER ( ALL ( Table1 ), Table1[Index] = LastNonBlankIndex)
    )
    pretodate = 
VAR LastNonBlankIndex =
    CALCULATE (
        LASTNONBLANK ( Table1[Index], 1 ),
        FILTER (
            ALL ( Table1 ),
           Table1[Index] <= EARLIER ( Table1[Index] )
                && NOT ( ISBLANK ( Table1[coupon valid from]) )
        )
    )
RETURN
    CALCULATE (
        MAX(Table1[coupon valid till]),
        FILTER ( ALL ( Table1 ), Table1[Index] = LastNonBlankIndex )
    )
    check = IF(AND(Table1[date]<=Table1[pretodate],Table1[date]>=Table1[prefromdate])&&Table1[coupon code]=BLANK(),TRUE())



    Regards,
    Lydia

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    aJ2,

    Please create the following columns in your table.

    Index = RANKX(FILTER(Table1,Table1[order id]<EARLIER(Table1[order id])),Table1[order id],,ASC)
    prefromdate = 
VAR LastNonBlankIndex =
    CALCULATE (
        LASTNONBLANK ( Table1[Index], 1 ),
        FILTER (
            ALL ( Table1 ),
           Table1[Index] <= EARLIER ( Table1[Index] )
                && NOT ( ISBLANK ( Table1[coupon valid from]) )
        )
    )
RETURN
    CALCULATE (
        MAX(Table1[coupon valid from]),
        FILTER ( ALL ( Table1 ), Table1[Index] = LastNonBlankIndex)
    )
    pretodate = 
VAR LastNonBlankIndex =
    CALCULATE (
        LASTNONBLANK ( Table1[Index], 1 ),
        FILTER (
            ALL ( Table1 ),
           Table1[Index] <= EARLIER ( Table1[Index] )
                && NOT ( ISBLANK ( Table1[coupon valid from]) )
        )
    )
RETURN
    CALCULATE (
        MAX(Table1[coupon valid till]),
        FILTER ( ALL ( Table1 ), Table1[Index] = LastNonBlankIndex )
    )
    check = IF(AND(Table1[date]<=Table1[pretodate],Table1[date]>=Table1[prefromdate])&&Table1[coupon code]=BLANK(),TRUE())



    Regards,
    Lydia