Sales for 2014 and 2015 but only for date interval selected by user

[sanchoniathon]

Hi,

Hello to all, i need the following display of report:

|SalesRepNo | Part# | Sales$ 2014| Sales$ 2015 |

The report needs to allow the user to select a date interval for 2014 (will impact Sales$ 2014 column) as well as a date interval for 2015 (will impact Sales$ 2015 column)
and will display appropriate sales.

Is this possible ?

Thank you for any valuable advices !

[bvk.cognoise]

Hi Sanchoniathon,

Please follow below steps

>create two queries with required columns

>apply hard code filter in one query for 2014 and filter second query for 2015

Year=2014
Year=2015

>And also inside each query add date filter with parameters

Eg: Date between ?a? and ?b?

> Then create join on these two queries finally drag Sales query item from both queries into join query .Name them as 2014 Sales and 2015 Sales

Then use this join query to your report object.


Regards
Bvk

[Lynn]

You did not specify whether your source is relational or dimensional. Response below assumes relational.

You can do this with one query. Include sales rep and part number, then add a query item for 2014 using this expression:

Code Select Expand


case
  when [Your Date] between ?2014_Begin? and ?2014_End? then [YourSalesMeasure]
  else 0
end


Do the same thing for 2015 replacing the parameter names as appropriate.

Create a filter for the query as follows:

Code Select Expand


[Your Date] between ?2014_Begin? and ?2014_End?
or
[Your Date] between ?2015_Begin? and ?2015_End?


I'd further suggest you can make this more generic by using Year1 and Year2 instead of 2014 and 2015. Then create a data item to extract the year from one of the year1 parameters and use it as the source for the column heading. Do the same for year2. In this way the report will work for any two years the user cares to compare.

The solution that bvk.cognoise provided is an option, but you might lose data if you do an inner join between the two queries and there are reps/part combinations in one year but not the other. Further, separate queries that are combined within Report Studio are going to mean the results get processed locally on the Cognos server rather than back at the database. Depending on the result set sizes this may not perform well.

[sanchoniathon]

Thanks to both of you for both suggestions !

I will surely try this soon enough.
:)

[sanchoniathon]

I had to add the [Date] column on the properties of my list object  (because we don't need to display ) and therefore the 2 sales $ columns are now working correctly.

Thank you so much for your fast replys.

Other question:

I now, need to display as the last column the [Variation%] column but it doesn't seems to work as for when:
1- Create a new data item
2- and include the following calculation: CurrentYearSales$/LastYearSales
3- Add percent format to it
4- Add that newly create data item to my list to display it.

But i don't get the expected results.
Anyone has any advices ? Can i still manage to this simple data item calculation in the same query ?

[sanchoniathon]

Anyone have any advice? Can I still manage to this simple data item calculation in the same query?


Also, i want the following result display:
________________________________________________________
| Client# | ClientName       | Sales2014 | Sales2015    | Variation%    |
------------------------------------------------------------------------------
|100000 | JohnJohn           | 7 000.00$   | 8 000.00 $  |     %            | 
-------------------------------------------------------------------------------       
|200000 | MonicaMonica   | 4 000.00$   | 6 000.00$      |      %          |



But instead get the following more detailed list:

__________________________________________________________
| Client# | ClientName       | Sales2014 | Sales2015    | Variation%       |
--------------------------------------------------------------------------------
|100000 | JohnJohn           | 3 000.00$   | 5 000.00 $     |     %            |         
                                        | 4 000.00$   | 3 000.00 $     |      %           |
---------------------------------------------------------------------------------
|200000 | MonicaMonica   | 4 000.00$   | 6 000.00$        |       %          |

etc...

We have a grouping on the Cient# as well as on the ClientName. It looks like it reacts to the fact that i had to add the DATE column as a PROPERTY of the LIST as for i wasn't getting the correct numbers on the Sales columns. The DATE column is not displayed on the LIST (intentionnally)

Thanks in advance for any valuable hints.