topic Average Aggregation Gauge Issue in QlikView

Average Aggregation Gauge Issue

Thu, 19 Jul 2012 16:27:04 GMT

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

brenner_martina — Mon, 23 Jul 2012 09:36:20 GMT

Hi,

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

Greetings from Munich

Martina

Re: Average Aggregation Gauge Issue

Mon, 23 Jul 2012 15:56:37 GMT

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

brenner_martina — Mon, 23 Jul 2012 16:15:01 GMT

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

Mon, 23 Jul 2012 16:26:49 GMT

Great, this worked. Thank you so much!

Re: Average Aggregation Gauge Issue

Wed, 25 Jul 2012 20:28:12 GMT

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

brenner_martina — Thu, 26 Jul 2012 07:13:42 GMT

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

Thu, 26 Jul 2012 12:15:34 GMT

Thank you again! This works.

Regards,

Ben D.