7 Replies Latest reply: Jul 26, 2012 8:15 AM by Benjamin Dykstra

# Average Aggregation Gauge Issue

Hello:

I am having an issue with one of my gauges--if two years are not selected when trying to compare previous years, the expression returns null. It works fine comparing the current and previous year (2012 vs 2011). While the expession works if you select both of the years you want to compare, this is not ideal.

An additional note: the other gauges on this sheet work fine when comparing previous years; however, none of the other gauges are using Avg(Aggr(

If(Max(CCYTD)=1,

(\$(vCCYTDOnHandDol) - \$(vPCYTDOnHandDol) / \$(vPCYTDOnHandDol),

(\$(vMaxCYOnHandDol) - \$(vMax-1CYOnHandDol) / \$(vMax-1CYOnHandDol)

CCYTD is a flag for the current calendar year to date. PCYTD is a flag for the previous calendar year to date.

vCCYTDOnHandDol: Avg(Aggr(Sum({\$<CCYTD={1}>}OnHandDol),POSDate))

vPCYTDOnHandDol: Avg(Aggr(Sum({\$<PCYTD={1}>}OnHandDol),POSDate))

vMaxCYOnHandDol: Avg(Aggr(Sum({\$<CYYearNum={\$(=Max(Total CYYearNum))}>}OnHandDol),POSDate))

vMax-1CYOnHandDol: Avg(Aggr(Sum({\$<CYYearNum={\$(=Max(Total CYYearNum-1))}>}OnHandDol),POSDate))

Is there any way to fix this expression? Any help would be greatly appreciated. See attachments for a look at the gauges.

• ###### Re: Average Aggregation Gauge Issue

Hi,

I tried to understand your expressions, but see my example.

Greetings from Munich

Martina

• ###### Re: Average Aggregation Gauge Issue

Hello Martina:

Thank you for taking the time to try to address my problem. Unfortunately, your example still experiences the same issue I am having in my object--when trying to compare anything but the current and previous, you must select both of the years you want to compare. If you only select one year, the gauge does not work.

I was hoping to find something that would compare the max year (based on the current selection) to the previous year. So say if you select 2011, then in the gauge it would compare 2011 to 2010.

Any further suggestions?

Thanks,

Ben D.

• ###### Re: Average Aggregation Gauge Issue

Hi, Ben D.,

pardon, then you have to enter {<Year=>} in your expression after AVG of the variable with the minYear:

Greetings from munich

Martina

• ###### Re: Average Aggregation Gauge Issue

Great, this worked. Thank you so much!

• ###### Re: Average Aggregation Gauge Issue

Hello:

While my original question was solved, I am experiencing another small issue--When trying to use this same formula, but only looking at Fridays, it is giving me the same problem--both years need to be selected in order for it to work.

Avg({<CYYearNum=>}Aggr(Sum({\$<CYYearNum={\$(=Max(CYYearNum)-1),POSDate={"=WeekDay(POSDate)=4"}>}OnHandDol),POSDate))

I tried to fix it like this, but the syntax isn't right:

Avg({<CYYearNum=,POSDate=>}Aggr

Any ideas on how to fix this? Thank you in advance!

Regards,

Ben D.

• ###### Re: Average Aggregation Gauge Issue

Hi Ben D.,

create a field in your script:

Weekday(POSDate) As WeekdayPOSDate

then you can use it in your Set Analysis:

Avg({<CYYearNum=>}Aggr(Sum({\$<CYYearNum={\$(=Max(CYYearNum)-1),WeekdayPOSDate={4}>}OnHandDol),POSDate))

Greetings from Munich

Martina

• ###### Re: Average Aggregation Gauge Issue

Thank you again! This works.

Regards,

Ben D.