Announcements
cancel
Showing results for
Did you mean:
Creator III

## Trouble using aggr() for YTD Revenue comparison unless both years are selected

I have a chart that shows MTD, QTD and YTD Revenue calculations for the sales group.  I use the aggr function because at a customer basis, the split between salespeople can vary even at the shipment (load) level depending on the sales number used. Hence my expression looks like this:

=  sum({<FYear={\$(=(vMaxSelectedYear)-1)},FQtr={\$(vCurrFQtr)},

aggr(

(Revenue * (PSalesNumRate/100))

, Customer_Name, load_key, SALESPRONAME, PSalesNumber  ) )

This works fine when I either have no FYear selection OR I select both 2014 and 2013, for example.

My vMaxSelectedYear variable gets the max year if none is selected.

The problem is that if I have just one year selected (2014) it gives me zero for 2013 Revenue.

I tried putting a 1 in the Set Analysis but it still gave me zero: "sum({<FYear={1(=(..."

Curiously for my load count (to which I have not applied the split percentage (PSalesNumRate), it works either way.

=Count({<FYear={\$(=(vMaxSelectedYear)-1)},FQtr={\$(vCurrFQtr)},

All I want is for it to work regardless of whether there is one, >1 or no years selected, basing prev year on max year selected (or not selected).

1 Solution

Accepted Solutions
Not applicable

Hi Tony,

I understand, that for each combination of:

you need to calculate expression:

Revenue * PSalesNumRate/100

i dont know your data model,  but understand, that there is more combinations of dimensions used in aggr than rows with Revenue * PSalesNumRate.

In your case you need to use set analysis expression in expression inside aggr.

try:

sum({1}aggr(only(

{<FYear={\$(=(vMaxSelectedYear)-1)},FQtr={\$(vCurrFQtr)},

Revenue * PSalesNumRate/100),Customer_Name, load_key, SALESPRONAME, PSalesNumber))

regards

Darek

2 Replies
Not applicable

Hi Tony,

I understand, that for each combination of:

you need to calculate expression:

Revenue * PSalesNumRate/100

i dont know your data model,  but understand, that there is more combinations of dimensions used in aggr than rows with Revenue * PSalesNumRate.

In your case you need to use set analysis expression in expression inside aggr.

try:

sum({1}aggr(only(

{<FYear={\$(=(vMaxSelectedYear)-1)},FQtr={\$(vCurrFQtr)},