Differentiating between excel files ? Time stamp??

[#1]

Hello Guys,

I pulled up two excel spreadsheets for year 2009 and 2010, concatenated them because both have same columns (fields) just the diferent data. Now I am drawing a graph using data from both the files.

I have to draw a graph showing the comparison between 2009 and 2010 data. Its like displaying how many male and female a company hired in 2009 and then 2010. But I am facing 2 problems:

1) I added "year" as an extra column in both the sheets, because there is no column which we can use to differentiate the data of 2009 and 2010. Now I am using "Sex" and "Year" field as dimension and count (empID) as expression. I can show the comparison but when I click on Relative checkmark to show my comparison in %, its taking both the year data as a single piece and showing percentage. Eg.

I have total data eg. 11000 records for 2009 and 12000 fro 2010. Now when i used concatenate , QV is treating it as a single table and then based on that its drawing a chart which gives following info:

2009: M: 40%

          F:10%

2010: M:45%

          F: 5%

So its not taking as 100% for each year individually. ?? I want to display like 2009: M: 80% F: 20%

                                                                                                               2010: M:70% F:30%

Is it a way to do that?

2) In futue I am gonna get these 2009 and 2010 data files directly from database.But still there is no way of differentiting between them as we don't have any column "Year". Is there way like while we pull data files from database we can add a timestamp ot something that can diff . between data for 2009 or 2010?

Thank you

Best,

Yaman

[#2]

Yama,

For your 1st question regarding % contribution, have a look at the attached application and for 2nd question, you have mentioned that you are going to load data from Database. Are the data for each year would be coming from different tables?

- Sridhar

[Anonymous]

Hey Yaman,

I put together a quick example for you which uses an expression like:

Sum(Students)/Sum(Total <Year> Students)

and then format it to show as a percentage.

[#4]

Johannes, Sridhar:

Thank you guys for your reply. But I am not getting the percentage still. Its like i dont have ano of hires column , so what I am doing is using this statement:

Count([Emp #])/Count(Total<Year> [Emp #]).

So I am getting a grraph..which is displaying again the same thing like total of 100%...then

2009: M=38%

F=12%

2010: M-37%

F-13%

Its displaying on different bars....and Y-axis has range till 60%.

What I want is that, it should display y-axis as 100% and then in the 2009 bar it should display like:

M=70% F=30% (I am using a stack graph)

and same for 2010.

I hope I am clear in my explaintation.

Thank you. I would appreciate your replies.:)

ps: @sridar: 2nd Question: its just a point in time table and the current system just pulls the yearly report once a year. So i think its just coming from a single table?

Yaman

[Anonymous]

Hi Yaman,

I don't understand what is wrong with the example I provided.. except that you mention something about using a stacked bar chart.

Best approach is if you upload your example and someone can help you modify it.