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

[tschullo]

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)},

ShipDate={"<=$(=addmonths(vCurrDateSelectedYear,-12))"}>}

  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)},

ShipDate={"<=$(=addmonths(vCurrDateSelectedYear,-12))"}>} cnt_key)

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).

Thanks in advance for your help!

[#2 · Accepted solution]

Hi Tony,

I understand, that for each combination of:

Customer_Name, load_key, SALESPRONAME, PSalesNumber

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)},

ShipDate={"<=$(=addmonths(vCurrDateSelectedYear,-12))"}>}

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

regards

Darek

[tschullo]

Thanks for the quick response - works like a charm!