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

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-03T18:08:30Z  
    This reply was deleted by Rick_J 2013-07-03T18:43:16Z. Reason for deletion: duplicate entries

    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

     

  • OpherB
    OpherB
    664 Posts

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

    ‏2013-07-03T17:06:12Z  

    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

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

    ‏2013-07-03T17:40:44Z  
    • OpherB
    • ‏2013-07-03T17:06:12Z

    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

     

    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.

  • OpherB
    OpherB
    664 Posts

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

    ‏2013-07-03T18:08:30Z  
    This reply was deleted by Rick_J 2013-07-03T18:43:16Z. Reason for deletion: duplicate entries

    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

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

    ‏2013-07-03T18:49:43Z  
    • OpherB
    • ‏2013-07-03T18:08:30Z

    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

     

    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

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

    ‏2013-07-03T18:56:39Z  
    • Rick_J
    • ‏2013-07-03T18:49:43Z

    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.. 

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

    Glad I could help.

    Opher