How to calculate an expression on based of a Dimension which is not present in the table

[#1]

Hello QA Community,

I am new in Qlikview and need a help for a problem.

In my report I have "year", "month" and "Deal Created by" as the dimension objects and I need two measure objects that are "count of deal created by" and "count of deal closed by". Since I can not pull the Deal Closed by Dimnesion in the table so I am not getting the proper result of the count of closed by deal data. Could you pls help me to resolve the issue?

Currently I am using count of deal closed by="aggr(count(Deal_TakedownID),Deal_Closeby" as a formula.

Note: For example Some deals are created by A but closed by either A or B or C

Pls let me know if you need any more information.

Thanks,

[swuehl]

Advanced aggregation aggr() is probably the way to go, but you might need to rethink which dimensions to use in the advanced aggregation (maybe you need to add Deal_CreatedBy, too, and / or your calendar dimensions).

Also you probably need to add an aggregation function around the aggr(), maybe like

=sum( aggr( count(Deal_TakedownID), Deal_CloseBy))

I haven't fully understood your requirement and setting, it would be helpful if you can post a small sample app together with your expected outcome (e.g. in an excel table).

[#3]

Thanks for your answer. For example I have the below data :

Account Schedule Number	Year	Month	Created By	Closed By
10001	2013	June	Melissa	Dana
10002	2013	June	Melissa	Tammy
10003	2013	June	Melissa	Tammy
10004	2013	June	Dana	Tracy
10005	2013	June	Melissa	Regina
10006	2013	June	Melissa	Melissa

And I want to show data in the following way:

Year	Month	Created By	Total Created #	Total Closed#
2013	June	Melissa	5	1
2013	June	Dana	1	1
2013	June	Tammy	0	2
2013	June	Tracy	0	1
2013	June	Regina	0	1
Total			6	6

Thanks,

Goirik