Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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
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)