none
Getting This week and last week data

    Question

  • Hello,

    I have booking fact table and I want to see how many booking the employees booked this week compare to last week. I tried many examples I found in the forum but not getting the right results.

    I tried:

    WITH 
    SET LastWeek AS ClosingPeriod([Book Date].[Week Number Of Year].[Week Number Of Year], [Book Date].[Week Number Of Year].currentmember)
    SET Last13Weeks AS {LastWeek.Item(0).Lag(2):LastWeek.Item(0)}
    MEMBER MEASURES.IIFDEMO AS IIF([Measures].[No Pax]>0, [Measures].[No Pax], "0")
    SELECT Last13Weeks*MEASURES.IIFDEMO ON 0,
    NON EMPTY ORDER(FILTER ( [Pax Report].[Booked By].[Booked By] , MEASURES.IIFDEMO <> "0") , MEASURES.IIFDEMO,DESC) ON 1
    FROM [Cube]
    WHERE {[Pax Report].[Res Status].&[A]}
    

    But then I get the last weeks from the list: 8 and 9 (because it is acting as string so my weeks go 1,10,11,12,2...8,9)

    Thanks for the help

    Tuesday, July 23, 2013 7:48 PM

All replies

  • Hi

    To my understanding of your question (sorry, bit not clear to me)

    1. You need to get the last week member and previous last 3 weeks information

    try this,

    WITH 
    MEMBER LastWeek AS 
    		TAIL( nonempty( [Book Date].[Week Number Of Year].[Week Number Of Year].Members ,[Measures].[No Pax] )).ITEM(0)	
    
    
    SELECT { [Measures].[No Pax] } ON 0,
    
    NON EMPTY {
        ORDER ( {  
    			   [Pax Report].[Booked By].[Booked By].Members *
    			   {   LastWeek        
    				 : LastWeek.lag(2)
    			   }
    			   } 
    			   , [Measures].[No Pax]
    			   , DESC
    			)
    			    
    	      }     ON 1
    FROM [Cube]
    WHERE {[Pax Report].[Res Status].&[A]}


    Prav

    Wednesday, July 24, 2013 6:43 AM
  • seems like a sorting issue with your attributes to me

    can't you convert your relational column to int and sort the final Attribute "by Key"?


    - www.pmOne.com -

    Wednesday, July 24, 2013 7:08 AM
    Answerer
  • Hi

    To my understanding of your question (sorry, bit not clear to me)

    1. You need to get the last week member and previous last 3 weeks information

    try this,

    WITH 
    MEMBER LastWeek AS 
    		TAIL( nonempty( [Book Date].[Week Number Of Year].[Week Number Of Year].Members ,[Measures].[No Pax] )).ITEM(0)	
    
    
    SELECT { [Measures].[No Pax] } ON 0,
    
    NON EMPTY {
        ORDER ( {  
    			   [Pax Report].[Booked By].[Booked By].Members *
    			   {   LastWeek        
    				 : LastWeek.lag(2)
    			   }
    			   } 
    			   , [Measures].[No Pax]
    			   , DESC
    			)
    			    
    	      }     ON 1
    FROM [Cube]
    WHERE {[Pax Report].[Res Status].&[A]}


    Prav

    Thank you for the reply.

    I'm trying to get something ever easier. Compare this week to last week number of booking by sales ref. 

    When I run your query I get error: 

    Executing the query ...
    A calculated member cannot be used as an operand of the Range operator ( : ).
    Execution complete

    Wednesday, July 24, 2013 12:39 PM
  • seems like a sorting issue with your attributes to me

    can't you convert your relational column to int and sort the final Attribute "by Key"?


    - www.pmOne.com -

    Hi Gerhard,

    I'm not sure why SSAS treats the week's column as string.

    In the DB the [WeekNumberOfYear] column is tinyint. 

    Any ideas why?

    Thanks

    Wednesday, July 24, 2013 12:44 PM
  • there is a property of the Attribute called "OrderBy"

    it should be "Key"
    if it is "Name" it may happen it is sorted in alphanumeric order


    - www.pmOne.com -

    Wednesday, July 24, 2013 1:29 PM
    Answerer
  • there is a property of the Attribute called "OrderBy"

    it should be "Key"
    if it is "Name" it may happen it is sorted in alphanumeric order


    - www.pmOne.com -

    Thank you, it doesn't sort by the correct values.

    But still gives me the end of the year.

    How do I get this week and last week.

    When I do:

    WITH 
    SET LastWeek AS ClosingPeriod([Book Date].[Week Number Of Year].[Week Number Of Year], [Book Date].[Week Number Of Year].currentmember)
    SET Last13Weeks AS {LastWeek.Item(0).Lag(4):LastWeek.Item(0)}

    I get the last four weeks of the year (weeks 50-53), not this week and previous three. 

    Also, I'm getting a sum of booking for these weeks for all years (so instead of summing this year values, I get from 2008- today for specific week)

    Please advise, thanks

    Wednesday, July 24, 2013 2:29 PM
  • well, according to your Attributename [Week Number Of Year] i would assume that this is Independent of years

    this means [Week Number Of Year].&[1] would be an Aggregation over all 1st Weeks of all years

    couldn't you simply add the year into the WHERE-clause of your query??


    - www.pmOne.com -

    Wednesday, July 24, 2013 3:26 PM
    Answerer
  • Yes of course I can. But how do I add:

    Where [Book Date].[Calendar Year] = This Year

    To my where clause? I'm not sure how to make it dynamic. 

    However, it's still not giving me the right result set. Today is week 30 of the year. The result should be weeks 27-30 not 50-53.

    Also, what will happen if we run this query on the first week of the year? it will not be able to compare to last week since last week is a different year.

    Thanks again

    Wednesday, July 24, 2013 3:34 PM