Aggr query

[#1]

Hello,

I have a complex query I am looking help with and would appreciated suggestions how to get it resolved. In the table below I have multiple test sets, and test cases which are attached to each test set (a test set is above a test case in the hiearchy and a test case can be in different test sets, e.g. Test case ID 1).

What I want to find out is the number of Test sets that have been successfully passed (i.e. all test cases within a test set must have a status of "Passed") and also the date at which each have been completed (the date to use would be the max Test case complete date). I want to display the information in a text box and a bar chart. As per my example I have 2 test sets that are passed (Testset1 with a complete date of 21/01/12 and Testset4 with a complete date of 17/02/12).

To get around the issue of Test case ID's being displayed in mulitple Test sets I created a new field and simply mergerd Test Set and Test case ID. I think aggr is the most appropiate method to resolve this but am having issues with this.

Thanks,

Ralph

Test set	Test case ID	Test case status	Test case complete date
Testset1	1	Passed	20/01/12
Testset1	2	Passed	20/01/12
Testset1	3	Passed	21/01/12
Testset2	4	Passed	06/02/12
Testset2	5	Failed	06/02/12
Testset3	6	Failed	14/02/12
Testset3	1	Passed	15/02/12
Testset3	7	Not completed	16/02/12
Testset4	2	Passed	17/02/12

[jagan]

Hi,

Please check the attachment for solution.

Regards,

Jagan.

[swuehl]

I think, Jagan solution is just fine.

If you want to play around with aggr() (which you don't necessarily need to, see above) but I find it is interesting to play around with different approaches), you could use a dimension Test set and as expression:

if(

sum(aggr(if([Test case status]<>'Passed',1),[Test set],[Test case ID]))

,0

,FirstSortedValue([Test case complete date],-[Test case complete date])

)

Regards,

Stefan

[#4]

Many thanks for that, it works in a straight table and a text box. However I can't seem to get it working for a bar chart, am I missing something very simple (which is likely) or can this be done?

Thanks,

Ralph

[swuehl]

What is your dimension in the bar chart? And what do you want to show as y-value (I just assume the Date is not very useful here)?

[#6]

We want to track the date at which Test Set passes and would have to use the max date of the test case within the test set to show this. I'm thinking some script work would have to be done for this to be shown?

Thanks,

Ralph

[jagan]

Hi,

What is your dimension?  If it TestSet then you will get 1as pass count for each bar for those Testset who passed all the Test cases.  I think date is not used as Y value.

=Sum(Aggr(If(Count(TestcaseID) = Count({<Testcasestatus={'Passed'}>}TestcaseID), 1, 0), Testset))

Regards,

Jagan.