Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

1 Solution

Accepted Solutions
brenner_martina
Partner - Specialist II
Partner - Specialist II

Hi, Ben D.,

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

VRAIBLE.png

Greetings from munich

Martina

View solution in original post

7 Replies
brenner_martina
Partner - Specialist II
Partner - Specialist II

Hi,

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

Greetings from Munich

Martina

Not applicable
Author

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.

brenner_martina
Partner - Specialist II
Partner - Specialist II

Hi, Ben D.,

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

VRAIBLE.png

Greetings from munich

Martina

Not applicable
Author

Great, this worked. Thank you so much!

Not applicable
Author

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.

brenner_martina
Partner - Specialist II
Partner - Specialist II

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

Not applicable
Author

Thank you again! This works.

Regards,

Ben D.