Find Weekstart day Value from Date

[muthukumar77]

Hi,

I have Order date and Sales Amount in Order details table. I need to find out the sales amount of weekstart day.

If any Weekstart day value is null, take before day sales value.

[sunny_talwar · Accepted solution]

For just seeing the weekstart values, you can create a flag in the script:

LOAD OrderID,

    ShipperID,

    Date(OrderDate) as OrderDate,

    BudgetKeyEY,

    CustomerID,

    EmployeeID,

    Freight,

    ShipmentsKey,

    ShipperName,

    LineNo,

    ProductID,

    Quantity,

    UnitPrice,

    Discount,

    GrossSales,

    NetSales,

    ShipmentsKeyline,

    If(WeekStart(OrderDate) = OrderDate, 1, 0) as Flag

FROM

[OrderDetails.qvd]

(qvd);

and then use this expression

Sum({<Flag = {1}>}NetSales)

Do you have an example where WeekStart is null and you want to use previous day Sum(Sales).

[muthukumar77]

Hi,

Please refer excel sheet.

Suppose Weekstart day is MONDAY.

04/01/2016 is Monday. if amount of this order date valid is null, I need to fetch the previous order date value.

[muthukumar77]

Value - *Valid