Topic
  • 10 replies
  • Latest Post - ‏2013-12-18T20:42:21Z by Arhan
Rick_J
Rick_J
138 Posts

Pinned topic Sales Reports for Previous Day, Last 5 Days and MTD

‏2011-03-18T14:26:54Z |
I'm trying to create a crosstab report using a relational source and RS 8.3 that shows the previous day sales then the last 5 days and then a MTD. The report would run daily.

Row:
Product Description

Columns:
Previous Day Sales - (this would be the previous day - 3/17/2011)
Previous Week Sales - (this would be each of the 5 days prior to 3/17 - (3/16, 3/15, 3/14, 3/13, 3/12)
MTD Sales -(Beginning of current month until current day)

I've created a screen-shot of what I'm looking for and it probably makes more sense looking at it.

I'm getting hung up on whether I need to create three separate queries which I cant get them numbers to tie. I've also tried creating an IF statement and filtering on the transaction date for each date range:

Previous Day = total( if ( Transaction Date = current_date, -1 ) then ( Sales ) else ( 0 ) )
Previous Day 1 = total( if ( Transaction Date = current_date, -2 ) then ( Sales ) else ( 0 ) )

and so on for each of the days and then one to grab since the beginning of the month _first_of_month( current_date )

Unfortunately I'm not able to work with a cube and only have the actual transaction date as the data item to work with. Any help would be greatly appreciated...
Updated on 2011-03-28T14:53:04Z at 2011-03-28T14:53:04Z by Rick_J
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Sales Reports for Previous Day, Last 5 Days and MTD

    ‏2011-03-22T02:49:56Z  
    If you are stuck with a relational model then you are probably best to go with a single query and the IF THEN ELSE statements. The single query should have a filter which spans the entire date range that you are interested in. The easiest thing to do would be to use something like:

    [Namespace].[Query Subject].[transaction date] between _add_days(current_date, -40) and _add_days(current_date, -1)
    


    Then you would create individual calculations for each period measure value. The Previous Day value would be obtained using:

    IF ([Namespace].[Query Subject].[transaction date] = _add_days(current_date, -1)) THEN ([Sales]) ELSE (null)
    


    The other days would be a modification of the _add_days function to move further back in time. The month value would use a between clause in the IF THEN ELSE condition. I think there might be a first/last of month date calculation on the Cognos side which you could leverage to get the start of the month. Otherwise, check your database docs to see what functions they may provide for getting the first day of the month for the between clause. There's no need for the total function in the IF THEN ELSE because the automatic grouping and aggregation will roll up the values for you.

    You will end up with individual calculations for each period which you can drag onto the crosstab columns.
    Updated on 2014-03-25T08:11:32Z at 2014-03-25T08:11:32Z by iron-man
  • Rick_J
    Rick_J
    138 Posts

    Re: Sales Reports for Previous Day, Last 5 Days and MTD

    ‏2011-03-22T19:21:09Z  
    Thank you again Phil for the help. I was failing to first bring in the time frame needed, but I see now how your doing it. Thanks again.
  • OpherB
    OpherB
    664 Posts

    Re: Sales Reports for Previous Day, Last 5 Days and MTD

    ‏2011-03-22T21:20:09Z  
    If you are stuck with a relational model then you are probably best to go with a single query and the IF THEN ELSE statements. The single query should have a filter which spans the entire date range that you are interested in. The easiest thing to do would be to use something like:

    <pre class="java dw" data-editor-lang="java" data-pbcklang="java" dir="ltr">[Namespace].[Query Subject].[transaction date] between _add_days(current_date, -40) and _add_days(current_date, -1) </pre>

    Then you would create individual calculations for each period measure value. The Previous Day value would be obtained using:

    <pre class="java dw" data-editor-lang="java" data-pbcklang="java" dir="ltr">IF ([Namespace].[Query Subject].[transaction date] = _add_days(current_date, -1)) THEN ([Sales]) ELSE (null) </pre>

    The other days would be a modification of the _add_days function to move further back in time. The month value would use a between clause in the IF THEN ELSE condition. I think there might be a first/last of month date calculation on the Cognos side which you could leverage to get the start of the month. Otherwise, check your database docs to see what functions they may provide for getting the first day of the month for the between clause. There's no need for the total function in the IF THEN ELSE because the automatic grouping and aggregation will roll up the values for you.

    You will end up with individual calculations for each period which you can drag onto the crosstab columns.
    I would comment on Phil's reply that, since you will SUM these sales amount, do not use NULL to terminate the IF statements, use 0 instead. This would greatly reduce the likelihood of getting a SUM of NULL later in your development process.

    IF ([Namespace].[Query Subject].[transaction date] = _add_days(current_date, -1)) THEN ([Sales]) ELSE (0)
    


    Regards,
    Opher
    Updated on 2014-03-25T08:11:21Z at 2014-03-25T08:11:21Z by iron-man
  • OpherB
    OpherB
    664 Posts

    Re: Sales Reports for Previous Day, Last 5 Days and MTD

    ‏2011-03-22T21:25:07Z  
    If you are stuck with a relational model then you are probably best to go with a single query and the IF THEN ELSE statements. The single query should have a filter which spans the entire date range that you are interested in. The easiest thing to do would be to use something like:

    <pre class="java dw" data-editor-lang="java" data-pbcklang="java" dir="ltr">[Namespace].[Query Subject].[transaction date] between _add_days(current_date, -40) and _add_days(current_date, -1) </pre>

    Then you would create individual calculations for each period measure value. The Previous Day value would be obtained using:

    <pre class="java dw" data-editor-lang="java" data-pbcklang="java" dir="ltr">IF ([Namespace].[Query Subject].[transaction date] = _add_days(current_date, -1)) THEN ([Sales]) ELSE (null) </pre>

    The other days would be a modification of the _add_days function to move further back in time. The month value would use a between clause in the IF THEN ELSE condition. I think there might be a first/last of month date calculation on the Cognos side which you could leverage to get the start of the month. Otherwise, check your database docs to see what functions they may provide for getting the first day of the month for the between clause. There's no need for the total function in the IF THEN ELSE because the automatic grouping and aggregation will roll up the values for you.

    You will end up with individual calculations for each period which you can drag onto the crosstab columns.
    Oh, my bad for not making this all into one reply....

    I would suggest the filter could be a little cleaner this way:

    [Namespace].[Query Subject].[transaction date] >= _first_of_month([Namespace].[Query Subject].[transaction date])
    


    Thus, you would only ever have this month's transactions to process.

    Regards,
    Opher
    Updated on 2014-03-25T08:11:15Z at 2014-03-25T08:11:15Z by iron-man
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Sales Reports for Previous Day, Last 5 Days and MTD

    ‏2011-03-22T21:44:33Z  
    • OpherB
    • ‏2011-03-22T21:25:07Z
    Oh, my bad for not making this all into one reply....

    I would suggest the filter could be a little cleaner this way:

    <pre class="java dw" data-editor-lang="java" data-pbcklang="java" dir="ltr">[Namespace].[Query Subject].[transaction date] >= _first_of_month([Namespace].[Query Subject].[transaction date]) </pre>

    Thus, you would only ever have this month's transactions to process.

    Regards,
    Opher
    If the current date is within the first 5 days of the month then this filter will cause problems with the calculations for the five days prior to the current date.
  • OpherB
    OpherB
    664 Posts

    Re: Sales Reports for Previous Day, Last 5 Days and MTD

    ‏2011-03-22T21:48:24Z  
    If the current date is within the first 5 days of the month then this filter will cause problems with the calculations for the five days prior to the current date.
    Good point, Phil. What is the requirement for the report during the first five days of the month? Flow into the prior month or just show whatever is in this month?

    I think the only "problem" is that the "last 5 days" might only show three or four days, depending on where we are in the month.

    Regards,
    Opher
  • Rick_J
    Rick_J
    138 Posts

    Re: Sales Reports for Previous Day, Last 5 Days and MTD

    ‏2011-03-25T18:26:17Z  
    "What is the requirement for the report during the first five days of the month? Flow into the prior month or just show whatever is in this month?"

    That is a good question. I've changed the data item from Transaction Date to Posted Date because our tickets arriving from remote sites and vendors are delayed and/or lost; changing to the posted date will ensure the sales are accounted for in the report eventually. In our situation we have 17 sites that perform a month-end closing process but not all at the same time. Some of these sites will have the necessary paperwork to close their month on the 1st, some will not until the 4th maybe 5th.

    In answer to your question, I would just say it needs to show through the last posting day until each location has completed their monthend process.

    The 6th - 30th/31st it's the current month.
    The 1st - 5th it may be the prior month from some Locations and current month for others.

    Currently I'm using the MTD column with:
    Namespace.Query Subject.posted date >= _first_of_month(current_date)

    Does that make any sense?
  • OpherB
    OpherB
    664 Posts

    Re: Sales Reports for Previous Day, Last 5 Days and MTD

    ‏2011-03-25T20:58:15Z  
    • Rick_J
    • ‏2011-03-25T18:26:17Z
    "What is the requirement for the report during the first five days of the month? Flow into the prior month or just show whatever is in this month?"

    That is a good question. I've changed the data item from Transaction Date to Posted Date because our tickets arriving from remote sites and vendors are delayed and/or lost; changing to the posted date will ensure the sales are accounted for in the report eventually. In our situation we have 17 sites that perform a month-end closing process but not all at the same time. Some of these sites will have the necessary paperwork to close their month on the 1st, some will not until the 4th maybe 5th.

    In answer to your question, I would just say it needs to show through the last posting day until each location has completed their monthend process.

    The 6th - 30th/31st it's the current month.
    The 1st - 5th it may be the prior month from some Locations and current month for others.

    Currently I'm using the MTD column with:
    Namespace.Query Subject.posted date >= _first_of_month(current_date)

    Does that make any sense?
    You wrote

    "... I would just say it needs to show through the last posting day until each location has completed their monthend process. "

    How is the report supposed to determine that each location has completed anything? Other than that, you seem to be doing it all correctly.

    Opher
  • Rick_J
    Rick_J
    138 Posts

    Re: Sales Reports for Previous Day, Last 5 Days and MTD

    ‏2011-03-28T14:53:04Z  
    I don't know. That's going to be an issue. Some Locations will close their month on the 1st and some on the 5th. So some will be reported on prior month and some in current month.
  • Arhan
    Arhan
    1 Post

    Re: Sales Reports for Previous Day, Last 5 Days and MTD

    ‏2013-12-18T20:42:21Z  

    I have the same issue with the cube, can some one please help. with the definitions?