Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

same dimension twice

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

19 Replies
Not applicable
Author

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

swuehl
MVP
MVP

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
Not applicable
Author

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

Not applicable
Author

So in conclusion I should get a table like below. (figures are not accurate)

 

SalesmanASalesmanBSalesman A sales compared to SalesmanB
Salesman2005Salesman200565.00
Salesman2005Salesman20074500.00
Salesman2005Salesman2008187.00
Salesman2006Salesman2006415.00
Salesman2006Salesman2007450.00
Salesman2006Salesman2008441.00
Salesman2006Salesman2009400.00
Salesman2007Salesman200565.00
Salesman2007Salesman2006400.00
Salesman2007Salesman20075197.00
Salesman2007Salesman2008906.00
Salesman2007Salesman2009652.00
Salesman2008Salesman200565.00
Salesman2008Salesman2006415.00
Salesman2008Salesman20075197.00
Salesman2008Salesman20088042.50
Salesman2008Salesman2009652.00
Salesman2009Salesman2006400.00
Salesman2009Salesman2007697.00
Salesman2009Salesman2008719.00
Salesman2009Salesman2009652.00
swuehl
MVP
MVP

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.

Not applicable
Author

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)

swuehl
MVP
MVP

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

Not applicable
Author

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.

swuehl
MVP
MVP

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.

Not applicable
Author

Exactly what I need Stefan.

thank you very much.