topic Re: Trouble using aggr() for YTD Revenue comparison unless both years are selected in QlikView

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

tschullo — Wed, 21 May 2014 22:33:40 GMT

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!

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

Thu, 22 May 2014 08:02:21 GMT

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

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

tschullo — Thu, 22 May 2014 19:06:56 GMT

Thanks for the quick response - works like a charm!