Commonly Used Date expression

    Hi QlikView fans,

     

    This is my first post, so forgive me for not being an expert. I would like to share examples of Date expression which are commonly used.

     

    I hope, this will help QlikView fans and will shave there valable time.


    Script used:

    LOAD

    OrderDate, [Order Number], Amount,
    year(OrderDate) as OrderYear,
    Month(OrderDate) as OrderMonth,
    Date(MonthStart(OrderDate),'MM') as MonthNumber,
    dual(year(OrderDate)&'Q'&Ceil(Month(OrderDate)/3),QuarterStart(OrderDate))as MyQuarter,
    if(month(weekend(OrderDate))=month(OrderDate),div(day(WeekEnd(OrderDate)),7),div(day(Weekend(OrderDate,-1)),7)+1)+1 as WeekNumber,
    Date(MonthStart(OrderDate),'YYYYMM')&if(month(weekend(OrderDate))= month(OrderDate),div(day(WeekEnd(OrderDate)),7),div(day(Weekend(OrderDate,-1)),7)+1)+1 as WeekSrNo,
    Date(monthstart(OrderDate),'MMM-YY') as MonthYear

    FROM
    [D:\Data\Sample.xlsx]
    (ooxml, embedded labels, table is Sheet1);

    LOAD

    Order_Number as [Order Number], Customer
    FROM
    [D:\Data\Sample.xlsx]
    (ooxml, embedded labels, table is Sheet5);

     

     

    Sample Data Screenshot:

     

    1.PNG

    2.PNG

    Filters on UI: OrderYear, WeekNumber, OrderMonth, MyQuarter,OrderDate

    Sheet object Used:

    1. 1. Bar chart Showing last 12 Months

    Object type: Bar Chart

    Dimensions: MonthYear

    Expression: =Sum({1<OrderDate={">=$(=MonthStart(AddMonths(Max(OrderDate),11)))<=$(=MonthEnd(Max(OrderDate)))"}>} Amount)

     

     

    1. 2. Bar chart Showing Quarter Comparison

    Object type: Bar Chart

    Dimensions: Customer

    Expression:

    Selected Quarter

    Label = =dual(year(max(OrderDate))&'Q'&Ceil((Month(max(OrderDate))/3)),QuarterStart(max(OrderDate)))

    Definition= Sum(Amount)

    Previous Quarter

    Label = =if(Ceil((Month(max(OrderDate))/3)-1)>=1,
    dual(year(max(OrderDate))&'Q'&Ceil((Month(max(OrderDate))/3)-1),QuarterStart(max(OrderDate))),
    dual(year(max(OrderDate))-1&'Q4',QuarterStart(max(OrderDate))))

    Definition =if(Ceil((Month(max(OrderDate))/3)-1)>=1,
    Sum({1<MyQuarter={"$(=dual(year(max(OrderDate))&'Q'&Ceil((Month(max(OrderDate))/3)-1),QuarterStart(max(OrderDate))))"}>}Amount),
    Sum({1<MyQuarter={"$(=dual(year(max(OrderDate))-1&'Q4',QuarterStart(max(OrderDate))))"}>}Amount))

     

     

    1. 3. Bar chart Showing Week Comparison

    Object type: Bar Chart

    Dimensions: Customer

    Expression:

           Selected Week:

           Label=    =max(WeekSrNo)

           Definition= Sum(Amount)

          

           Previous Week:

    Label= =if(Right(max(WeekSrNo),1)>1,max(WeekSrNo)-1,
    if(Right(max(WeekSrNo),1)=1,
    if(MID(MAX(WeekSrNo),5,2)='01',
    left(max(WeekSrNo),4)-1&Max({1<OrderYear={'$(=left(max(WeekSrNo),4)-1)'}>} MonthNumber)&Max({1<OrderYear={'$(=left(max(WeekSrNo),4)-1)'},MonthNumber={'$(=MID(MAX(WeekSrNo),5,2)-1)'}>} WeekNumber),
    left(max(WeekSrNo),4)&num(mid(MAX(WeekSrNo),5,2)-1,'00')&Max({1<OrderYear={"$(=left(max(WeekSrNo),4))"},MonthNumber={"$(=num(mid(MAX(WeekSrNo),5,2)-1,'00'))"}>} WeekNumber))))

    1. 4. Table showing Amount and Rolling N month Amount

    Object type: Straight / Pivot Table

    Dimensions: OrderMonth

    Expression:

           Total Amount:

           Definition= =sum(Amount)

          

           Rolling N Month Amount:

           Definition= rangesum(above(sum({1}Amount),0,3))

           N – Can be any number for rolling

     

    1. 5. Rolling N Months

    Object type: Text Object

    Expression: =Sum({1<OrderDate={">=$(=MonthStart(AddMonths(Max(OrderDate),-N)))<=$(=MonthEnd(Max(OrderDate)))"}>} Amount)

     

    N – Can be any number for rolling

     

    Sample Application: