Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
tschullo
Creator III
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)},

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!

1 Solution

Accepted Solutions
Not applicable

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

View solution in original post

2 Replies
Not applicable

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
Creator III
Creator III
Author

Thanks for the quick response - works like a charm!