
Re: same dimension twice
Stefan WÃ¼hl Nov 17, 2011 10:25 AM (in response to rizo98)rizo98,
I am not sure if this is the optimal way, but if you are looking for something like attached, at least I do it this way pretty often.
You don't need to copy the complete table, just the fields you need.
Hope this helps,
Stefan

Crosstabulation.qvw 144.5 K

Re: same dimension twice
rizo98 Nov 17, 2011 11:36 AM (in response to Stefan WÃ¼hl )I cant open the qv file as I only have the QV personal edition.
Would you be able to post the script?
But I do understand the idea of only copying the fields I need.

Re: same dimension twice
Stefan WÃ¼hl Nov 17, 2011 11:41 AM (in response to rizo98)Sure, quite a simple demo:
Transactions:
LOAD
recno() as TransID,
ceil(RAND()*100) as PersonID,
chr(floor(RAND()*10+65)) as Category
AutoGenerate 150;
LOAD
PersonID,
Category as Category2
resident Transactions;
Then I created a grid chart with dimensions Category and Category2 (note that you can relabel to have the same Name displayed), and expression
=if(Category<=Category2,count( distinct PersonID))
Regards,
Stefan

Re: same dimension twice
rizo98 Nov 17, 2011 12:36 PM (in response to Stefan WÃ¼hl )My problem is slightly different.
you will recall this thread.
http://community.qlik.com/message/166041#166041
I am trying to create a matrix table.
as shown in attached excel file
the QV is also attached.

Numbers.qvw 169.8 K

Excel.xls 24.5 K

Re: same dimension twice
Celambarasan Adhimulam Nov 17, 2011 12:58 PM (in response to rizo98)I couldn't get your problem exactly but check with this,
Load the same table twice by changing the field names and the used the required fields from the two table as dimension.
It will mostly a cross join.Regards,
Celambarasan

Re: same dimension twice
Stefan WÃ¼hl Nov 17, 2011 1:10 PM (in response to rizo98)Ok, I slightly remember our last yesterday's discussion. How do you read this table?
I assume rev is revenue and has something to do with Salesman ID, and Salesman ID1 determines the RevForDay, right?

Re: same dimension twice
rizo98 Nov 18, 2011 7:38 AM (in response to Stefan WÃ¼hl )I want the table to display this:
Display the individual sales totals for the days my colleagues and I have worked together
so e.g. 2006 has worked with 2005 for 5 days and during these 5 days, he has made sales of 45000, then this should be displayed in the table/pivot along with other indivuals who have also worked on the day(s).
I hope i'm clear

Re: same dimension twice
Stefan WÃ¼hl Nov 18, 2011 7:50 AM (in response to rizo98)Well, I get different numbers, in your example 79375 for those 5 days. If this is correct, try
=sum( aggr(if(sum([List Price]), sum(total<[Salesman ID],Day> [List Price])), [Salesman ID1],[Salesman ID],Day))
Stefan

Re: same dimension twice
rizo98 Nov 18, 2011 9:27 AM (in response to Stefan WÃ¼hl )Stefan,
79375 is the total for everybody together.
I would like totals by individual.
i hope i'm not putting you into too much bother
thanks

Re: same dimension twice
Stefan WÃ¼hl Nov 18, 2011 9:39 AM (in response to rizo98)Are you sure?
this is what I get for the 5 days for CustomerID 2006:
Salesman ID Day sum([List Price]) 79375 2006 1 4599 2006 4 4599 2006 7 1299 2006 9 41391 2006 11 27487 
Re: same dimension twice
rizo98 Nov 21, 2011 8:49 AM (in response to Stefan WÃ¼hl )sorry, this is probably doing your head in
All I want to show is the total of salesman sales against other salesman for the number of days they have worked together.
I dont need to show the days which they have worked together, just the total

Re: same dimension twice
rizo98 Nov 21, 2011 8:55 AM (in response to rizo98)So in conclusion I should get a table like below. (figures are not accurate)
SalesmanA SalesmanB Salesman A sales compared to SalesmanB Salesman2005 Salesman2005 65.00 Salesman2005 Salesman2007 4500.00 Salesman2005 Salesman2008 187.00 Salesman2006 Salesman2006 415.00 Salesman2006 Salesman2007 450.00 Salesman2006 Salesman2008 441.00 Salesman2006 Salesman2009 400.00 Salesman2007 Salesman2005 65.00 Salesman2007 Salesman2006 400.00 Salesman2007 Salesman2007 5197.00 Salesman2007 Salesman2008 906.00 Salesman2007 Salesman2009 652.00 Salesman2008 Salesman2005 65.00 Salesman2008 Salesman2006 415.00 Salesman2008 Salesman2007 5197.00 Salesman2008 Salesman2008 8042.50 Salesman2008 Salesman2009 652.00 Salesman2009 Salesman2006 400.00 Salesman2009 Salesman2007 697.00 Salesman2009 Salesman2008 719.00 Salesman2009 Salesman2009 652.00 
Re: same dimension twice
Stefan WÃ¼hl Nov 21, 2011 12:07 PM (in response to rizo98)sorry, this is probably doing your head in
All I want to show is the total of salesman sales against other salesman for the number of days they have worked together.
I dont need to show the days which they have worked together, just the total
I think I understand. I just wanted to ask you to recheck the total sum under question. For the common days that Salesman2005 and Salesman2006 have worked together, I get days 1,4,7,9,11. The above table is only to demonstrate that Salesman 2006 alone has a total sales for those days of 79375.
You said this is the value for all Salesman, and I was not able to confirm that.

Re: same dimension twice
rizo98 Nov 22, 2011 4:26 AM (in response to Stefan WÃ¼hl )Stefan,
"I think I understand. I just wanted to ask you to recheck the total sum under question. For the common days that Salesman2005 and Salesman2006 have worked together, I get days 1,4,7,9,11. The above table is only to demonstrate that Salesman 2006 alone has a total sales for those days of 79375."
the above is absolutely correct.
so Salesman2006 will have 79375 against Salesman2005.Salesman2005 will also have his 60181 in same table (table format above)

Re: same dimension twice
Stefan WÃ¼hl Nov 22, 2011 6:15 AM (in response to rizo98)Ok, but these are the numbers that my above posted expression
=sum( aggr(if(sum([List Price]), sum(total<[Salesman ID],Day> [List Price])), [Salesman ID1],[Salesman ID],Day))
is giving, aren't they?
Please have a look at attached sample.
Regards,
Stefan

Numbers2_SW.qvw 171.0 K

Re: same dimension twice
rizo98 Nov 22, 2011 7:18 AM (in response to Stefan WÃ¼hl )Stefan,
I have QV personal edition so I cannot opeb the file.
=sum( aggr(if(sum([List Price]), sum(total<[Salesman ID],Day> [List Price])), [Salesman ID1],[Salesman ID],Day))
This does the tirck. Thank you very much.
But now I cant get the total of Rev in table Rev to display next to AB in the attached sheet.

Numbers.qvw 171.0 K

Re: same dimension twice
Stefan WÃ¼hl Nov 22, 2011 10:16 AM (in response to rizo98)That was a bit tricky, please try
= num(sum(total<[Salesman ID]> aggr( if(sum({1}[List Price]), sum(total<Day> {1}[List Price])), [Salesman ID],Day,[Salesman ID])),'##0')
to get the Rev column in your table with both Salesman ID and Salesman ID1.
Hope this correct,
Stefan
edit:
a
= num(sum(total<[Salesman ID]> aggr( if(sum({1}[List Price]), sum(total<Day> {1}[List Price])), [Salesman ID],Day)),'##0')
should be enough, there was an additional Salesman ID dimension in aggr by mistake.

Numbers3_SW.qvw 171.5 K

same dimension twice
rizo98 Nov 22, 2011 12:57 PM (in response to Stefan WÃ¼hl )Exactly what I need Stefan.
thank you very much.
















Re: same dimension twice
Celambarasan Adhimulam Nov 17, 2011 12:29 PM (in response to rizo98)This will work.
For first dimension select the field normally.
For second dimension add calculated dimension as =fieldname.
Regards
Celambarasan