Topic
5 replies Latest Post - ‏2013-07-03T18:56:39Z by OpherB
Rick_J
Rick_J
138 Posts
ACCEPTED ANSWER

Pinned topic Compare Units for Current Month vs Last Year Current Month by Date

‏2013-07-03T09:23:15Z |

I need to create a report from a dimensional data source that will show the sales by weekday and date for the current month and last years current month. I've attached a screenshot of the desired outcome. This report will run daily. I've tried creating two separate queries and UNION the two but the data either repeats itself in my list report for LY Current Month or I get an incompatible data type error.

Any advice would be appreciated.

Attachments

Updated on 2013-07-03T09:25:45Z at 2013-07-03T09:25:45Z by Rick_J
  • OpherB
    OpherB
    664 Posts
    ACCEPTED ANSWER

    Re: Compare Units for Current Month vs Last Year Current Month by Date

    ‏2013-07-03T17:06:12Z  in response to Rick_J

    I think you are correct to make this two queries with a UNION.

    Start off with the two queries totally separate, put them into separate LIST objects and see what the results are. The incompatible data type issue is usually because the two do not have columns in the same order. They must match exactly.

    Good luck,
    Opher

     

    • Rick_J
      Rick_J
      138 Posts
      ACCEPTED ANSWER

      Re: Compare Units for Current Month vs Last Year Current Month by Date

      ‏2013-07-03T17:40:44Z  in response to OpherB

      I did just that and the overall totals for each time frame matched; the issue I'm running into now is some of the sales dates are in one year and not the next, which is to be expected. I need to list every day in the month regardless if there sales so the columns line up. If not then my comparisons wont correct if your scanning the list.

      for example: We didnt have sales on the 2nd in June 2013 but we did in 2012.

      June 2012 June 2013
      06-01-2012 06-01-2013
      06-02-2012 06-03-2013
      06-03-2012 06-04-2013
      06-04-2012 06-05-2013

      I tried creating a join to the Calendar -> Calendar Date and with a full outer join and use the Calendar Date in the List report and not the Sales Date but it still didnt give me all the dates. I'll keep working on I'm sure I'm overlooking the obvious.

    • This reply was deleted by Rick_J 2013-07-03T18:43:30Z. Reason for deletion: duplicate entries
    • This reply was deleted by Rick_J 2013-07-03T18:43:16Z. Reason for deletion: duplicate entries
      • OpherB
        OpherB
        664 Posts
        ACCEPTED ANSWER

        Re: Compare Units for Current Month vs Last Year Current Month by Date

        ‏2013-07-03T18:08:30Z  in response to Rick_J

        Right. A join will not work - you must do a UNION.

        Now you have two separate queries which have the correct dates and data, right?

        You need to add several columns to each query. the order is important, so here are the layouts -

        Let's say your queries are "Current_Year" and "Last_Year":

        "Current_Year"
        row_count
        CY_date
        LY_date '1900-01-01'
        day_of_week
        CY_units
        LY_units 0
        source 'C'

        "Last_Year"
        row_count
        CY_date '1900-01-01'
        LY_date
        day_of_week
        CY_units 0
        LY_units
        source 'L'

        This expands the queries so that there are default value columns for the data that will come from the other query.

        The columns with the date value of 1900-01-01 MUST match the other date elements, so you may have to CAST or other tweaks to get it right.

        The row count should give you 1, 2, 3, 4, 5... in each query. You'll use this to force a footer to appear and not display the detail rows.

        Sort Current_Year on CY_date and Last_Year on LY_date so that the dates will be in order.

        Now you can UNION these two queries. The result will be two rows for each "row number" value, one from "C" and one from "L".

        In your report group on row number and create a footer. In the footer, take the maximum() value for each of the two date columns and the day of week column. Then total the numeric values: there is a row with a value and a row with zero in each set, so you'll only get the "correct" total. Do the math for the Variance on the total elements and you're done.

        The only issue here will be when one year has more days of transactions than the other. Then there will be rows at the end without matching values from the other side. Take a look at them and decide how you want to process them, making changes to the above as needed.

        Then hide the details, add a summary total and you're golden!

        Good luck,
        Opher

         

        • Rick_J
          Rick_J
          138 Posts
          ACCEPTED ANSWER

          Re: Compare Units for Current Month vs Last Year Current Month by Date

          ‏2013-07-03T18:49:43Z  in response to OpherB

          Your right, thats golden!!  When I originally created the UNION I couldnt get the CY_ / LY_ date fields to work properly in the UNION query. I never thought about using the default value of '1900-01-01'. That's works perfect... thank you so much for helping me.. 

          • OpherB
            OpherB
            664 Posts
            ACCEPTED ANSWER

            Re: Compare Units for Current Month vs Last Year Current Month by Date

            ‏2013-07-03T18:56:39Z  in response to Rick_J

            I've only done that 371 times before...

            Glad I could help.

            Opher