0 Replies Latest reply: Feb 8, 2017 10:25 AM by Jorge Figueroa RSS

    How to compare with previous Week

    Jorge Figueroa

      Hi, my application has several dates and they all get extra fields because I Derive them from the original date field in the DB using the following script:

       

      [C]:

        DECLARE FIELD DEFINITION Tagged ('$date')

      FIELDS

        Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$year'),

        Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter'),

        Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$axis', '$yearquarter'),

        Month($1) AS [Month] Tagged ('$month'),

        Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth'),

        Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber'),

        Date(Floor($1)) AS [Date] Tagged ('$date');

      DERIVE FIELDS FROM FIELDS [InventoryDate], [TransactionDate], [BookingDate], [ActivityDate] USING [C] ;

       

       

      Now I need to compare when you select one week to the previous one. I am using only the Booking Date to filter results (I created a dimension that goes Year->month->date). So essentialy the user is selecting first the BookingDate.C.Year then must select the BookingDate.C.Week

       

      I tried using this but it doesn't seem to work. Im still getting the hang of set analysis. I was wondering if someone can help me fix the formula and explain a little of what each function is doing on the fixed formula.

       

       

      Sum({<BookingDate.C.Week = {'$(= "W"&Num(Week(BookingDate)-1,00)  )'}>} Sales)