Skip to Content
avatar image
Former Member

Bespoke Date/Week Variable Comparison

Hi,

I have a report which uses a bespoke date variable to define week numbers based on transaction dates. Why? Because my company uses Saturday to Friday as the week parameter, not Monday to Sunday as defined at Universe level (I have no access to the Universe to make amendments).

The variable is as follows:

=If([2016].[Store TD Date] Between ('26/12/2015';'01/01/2016');1; If([2016].[Store TD Date] Between ('02/01/2016';'08/01/2016');2; If([2016].[Store TD Date] Between ('09/01/2016';'15/01/2016');3... etc.

With an additional variable for the previous year.

I need to produce a simple table with 'Week Number' and '2015 Qty', but also a third column with '2016 Qty' where the values correspond with the week numbers from the '2015 Week' numbers.

I currently have 2 queries - one for each year - with the date variables defined for each year. But I have no way to merge the variables to create the link.

Any help would be much appreciated..

Best wishes,

Jamie.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    Dec 16, 2016 at 02:13 PM

    The functionality you're looking for is the ability to merge query results based on a variable. That was just introduced with BI4.2 SP3. See here: https://archive.sap.com/documents/docs/DOC-74364

    With that said, your definition of "week" is a perfect candidate for inclusion in the universe. I'm not sure what your reasons are for not being able to access it, but I think it's worth pursuing.

    The only other workarounds I can think of are quite hack-ish. One is to simply create two report blocks side-by-side, one each for 2015 and 2016. Positioned closely enough, they will appear to be a single block. You would not be able to create block-level variables that reference both queries, however.

    The other workaround is to modify both queries' SQL to include the week number. You would need to add any other numeric object to the query, then modify the query SQL to replace that object's definition with the week number. You could then merge the queries using that object.

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 16, 2016 at 01:08 PM

    First merge the Store TD Date objects from both the query then create single variable for Week on merged object.After that drag week variable & 2015 & 2016 QTY

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 16, 2016 at 01:42 PM

    Thanks Amit, but this produces a week number based on Monday to Sunday dates - the 'Week Number' in my report needs to be based on Saturday to Friday dates.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 20, 2016 at 09:08 AM

    Hi Joe, thanks for your feedback. Agreed. Ultimately, this needs to be implemented at universe level.

    The short-term fix that I have settled on is a separate spreadsheet containing date, day name, week and year - this associates the correct week number and year, based on the Saturday to Friday pattern. I've created a merged dimension using 'Store TD Date' and the date from the spreadsheet and this seems to work.

    Best wishes,

    Jamie.

    Add comment
    10|10000 characters needed characters exceeded