Set Analysis / Aggr statement help

[#1]

Here is my statement;

=SUM({1<Year={"$(=Only(Year))"},Division={"$(=Only(Division))"}>} AGGR(COUNT(DISTINCT DriverCode), WeekStart))

It all works except for the AGGR. I am trying to get the sum of the count of distinct driver codes as grouped by week. However, let's say I have 2 weeks selected, I am only getting the sum for the 2 weeks when I want the total for the whole year regardless of the selections. What am I missing in my formula to ignore the WeekStart selection and get the total for the year based on the year and division selected? As it stands, if I deselect the WeekStart field and leave only the Year and Division selected, i get the correct number

Thanks

[johnw]

I suspect that all you need to do is move the set analysis into the count() instead of on the sum().

[#3]

Changed statement to the following and still have the same problem:

=SUM(AGGR(COUNT({1<Year={"$(=Only(Year))"},Division={"$(=Only(Division))"}>} DISTINCT DriverCode), WeekStart))

Any other ideas?

Also, on a somewhat separate note, I have seen statements like the following (identical to the above with the highlighted difference);

=SUM(AGGR(COUNT({1<Year={"$(=Only(Year))"},Division={"$(=Only(Division))"}, WeekStart=>} DISTINCT DriverCode), WeekStart))

When the field (WeekStart) has no expression like that, what is it doing exactly?

Thanks John!

[johnw · Accepted solution]

When there's nothing after the "=", it means "ignore selections in this field". In your case, you're already using "1" as your base set, which says to ignore ALL selections. So there would be no reason to specifically ignore any specific field's selections.

You can try repeating the set condition in both the sum() and the count(). It doesn't seem like it should be necessary here, but I've run across cases where it was, so maybe it is.

[#5]

That did it... Placing the set condition in both the SUM() and AGGR() returned the correct number.

And thanks for the explanation on the other matter.

As always, thanks for sharing your expertise John!

[#6]

Hey John... One final question. That statement worked but had one unintended consequence. I am trying to use this expression in a pivot table/chart that is used to view data by week. Now, I am unable to select a limited range of weeks because of the 1<Year={"$(=Only([Year]))"} argument. So, I need the number/expression calculated on the year's worth of data, but I don't want to see all the weeks in the year. I still want to be able to select a smaller date range.. Can i add something to the expression to make this work?

Thanks.

(SUM({1<Year={"$(=Only([Year]))"},Division={"$(=Only([Division]))"}>} TermFlag)
/
SUM({1<Year={"$(=Only(Year))"},Division={"$(=Only(Division))"}>} AGGR(COUNT({1<Year={"$(=Only(Year))"},Division={"$(=Only(Division))"}>} DISTINCT DriverCode), WkStart)))
/7*365