topic Re: same dimension twice in QlikView

same dimension twice

Thu, 17 Nov 2011 15:07:25 GMT

Is the best/easiest/quickest way to use the same dimension twice is to create the same table again?

Re: same dimension twice

swuehl — Thu, 17 Nov 2011 15:25:07 GMT

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

Re: same dimension twice

Thu, 17 Nov 2011 16:36:08 GMT

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

swuehl — Thu, 17 Nov 2011 16:41:48 GMT

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 re-label to have the same Name displayed), and expression

=if(Category<=Category2,count( distinct PersonID))

Regards,

Stefan

Re: same dimension twice

CELAMBARASAN — Thu, 17 Nov 2011 17:29:35 GMT

This will work.

For first dimension select the field normally.

For second dimension add calculated dimension as =fieldname.

Regards

Celambarasan

Re: same dimension twice

Thu, 17 Nov 2011 17:36:23 GMT

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.

Re: same dimension twice

CELAMBARASAN — Thu, 17 Nov 2011 17:58:35 GMT

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

swuehl — Thu, 17 Nov 2011 18:10:55 GMT

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

Fri, 18 Nov 2011 12:38:09 GMT

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

swuehl — Fri, 18 Nov 2011 12:50:30 GMT

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

Fri, 18 Nov 2011 14:27:53 GMT

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

swuehl — Fri, 18 Nov 2011 14:39:14 GMT

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

Mon, 21 Nov 2011 13:49:18 GMT

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

Mon, 21 Nov 2011 13:55:24 GMT

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

swuehl — Mon, 21 Nov 2011 17:07:36 GMT

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

Tue, 22 Nov 2011 09:26:28 GMT

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

swuehl — Tue, 22 Nov 2011 11:15:04 GMT

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

Re: same dimension twice

Tue, 22 Nov 2011 12:18:07 GMT

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 A-B in the attached sheet.

Re: same dimension twice

swuehl — Tue, 22 Nov 2011 15:13:37 GMT

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:

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

same dimension twice

Tue, 22 Nov 2011 17:57:19 GMT

Exactly what I need Stefan.

thank you very much.