Aggregate functionality in set expression

[reema_dangwal]

Hi All,

Need help with the following query:

I have a table with columns as - incidents, groups and dates.
I need to count the number of incidents per week. However need to count only those incidents in a week which are more than 2 when grouped by group.

Please suggest how this can be achieved, can we use aggr function inside set expression?

PFA the data excel file and sample Qvw.

Example:

As shown below, this is how counts are represented per week.

week	count(Incidents)
	64
12/28/2015	1
2/1/2016	1
2/29/2016	6
3/7/2016	7
3/14/2016	7
3/21/2016	7
3/28/2016	7
4/4/2016	7
4/11/2016	7
4/18/2016	7
4/25/2016	7

but for week starting 2/29/2016, we have 6 incidents, in which 3 are from Group A, 2 - Group B and 1 - Group C.
So we need to show the count as 3 for 2/29/2016, since only Group A has a count more than 2.

114	Group A	01-03-2016
115	Group A	02-03-2016
116	Group A	03-03-2016
117	Group B	04-03-2016
118	Group B	05-03-2016
119	Group C	06-03-2016

Thanks

[swuehl · Accepted solution]

Maybe like attached, using a WeekGroupKey field created in the script and set analysis in your expression.

Weekstart	count({<WeekGroupKey = {"=Count(Incidents)>2"}>} Incidents)
	59
2/29/2016	3
3/7/2016	7
3/14/2016	7
3/21/2016	7
3/28/2016	7
4/4/2016	7
4/11/2016	7
4/18/2016	7
4/25/2016	7

[reema_dangwal]

Thanks Stefan, it works. However when i tried to implement the same on a bi-weekly basis, I am getting vague results.

PFA the test qvw file.

  The following incidents are falling under week - 2/15/2016 however they should fall under 2/29/2016.

Week        Incidents    Date          Group

2/15/2016	114	3/1/2016	Group A
2/15/2016	115	3/2/2016	Group A
2/15/2016	116	3/3/2016	Group A
2/15/2016	117	3/4/2016	Group B

I tried with date formatting too, not working.In order to represent date on a bi-weekly basis, shall I use something else apart from class function?

[swuehl]

I think you need the correct offset for your rounding / classifying, which should be any weekstart, not date(0), a saturday.

table:

LOAD *,

Weekstart(Floor(date,14,weekstart(Makedate(2000)))) as Weekstart,

AutoNumberHash256(Group, Weekstart(Floor(date,14,weekstart(Makedate(2000)))) ) as WeekGroupKey;

LOAD Incidents,

     Group,

     date

FROM

dummy.xlsx

(ooxml, embedded labels, table is Sheet1);