Compare AGGR Expression to Embedded AGGR

jasonfu — Sat, 16 Nov 2024 01:27:01 GMT

Given the dataset below, I've been trying (and failing) to calculate the number of bicycle transactions with a total price above the average of all bicycle transactions for each state. I would also like to be able to calculate the total dollars above the average.

It seems like the AGGR used in my inequality condition doesn't evaluate properly, but if I use the exact same AGGR Avg expression in its own expression in the straight table to check its results it calculates properly. So I feel like something funky is going on once it gets placed into the outer AGGR. Any help would be greatly appreciated!

Results I would like
I can't get Transactions > Avg or Dollars Over Avg to work

State	Bicycle Transactions	Bicycle Transaction Price	Avg Bicycle Transaction Price	Transactions > Avg	Dollars Over Avg
CA	4	920	230	2	240
OR	1	245	245	0	0
WA	1	200	200	0	0

Existing Expressions that work

Bicycle Transactions =COUNT(DISTINCT {$<Item={'Bicycle'}>} TransactionID)

Bicycle Transaction Price =SUM({$<Item={'Bicycle'}>}Price)

Avg Bicycle Transaction Price =AVG(AGGR(SUM({$<Item={'Bicycle'}>}Price), TransactionID))

Existing Expressions that don't work

Transactions > Avg
=SUM(
AGGR(
IF(
SUM({$<Item={'Bicycle'}>}Price)
> AVG(AGGR(SUM({$<Item={'Bicycle'}>}Price), TransactionID, State))
, 1
, 0
)
, TransactionID
)
)

Dollars Over Avg

=SUM(
AGGR(
IF(
SUM({$<Item={'Bicycle'}>}Price)
> AVG(AGGR(SUM({$<Item={'Bicycle'}>}Price), TransactionID, State))
, SUM({$<Item={'Bicycle'}>}Price)
- AVG(AGGR(SUM({$<Item={'Bicycle'}>}Price), TransactionID, State))
, 0
)
, TransactionID
)
)

Dataset

TransactionID	State	Item	Part	Price
1	CA	Bicycle	Wheel	100
1	CA	Bicycle	Helmet	200
2	CA	Bicycle	Handle	200
2	CA	Bicycle	Brake	200
3	CA	Bicycle	Brake	200
4	CA	Bicycle	Vest	20
5	CA	Skateboard	Wheel	300
5	CA	Skateboard	Board	50
6	CA	Skateboard	Wheel	400
7	WA	Bicycle	Wheel	200
8	WA	Skateboard	Wheel	50
9	WA	Skateboard	Board	600
9	WA	Skateboard	Shoes	100
9	WA	Skateboard	Helmet	200
10	WA	Skateboard	Board	200
11	OR	Bicycle	Wheel	50
11	OR	Bicycle	Helmet	75
11	OR	Bicycle	Brake	120

Table:
Load * inline
[
TransactionID, State,Item,Part,Price
1, CA, Bicycle, Wheel, 100
1, CA, Bicycle, Helmet, 200
2, CA, Bicycle, Handle, 200
2, CA, Bicycle, Brake, 200
3, CA, Bicycle, Brake, 200
4, CA, Bicycle, Vest, 20
5, CA, Skateboard, Wheel, 300
5, CA, Skateboard, Board, 50
6, CA, Skateboard, Wheel, 400
7, WA, Bicycle, Wheel, 200
8, WA, Skateboard, Wheel, 50
9, WA, Skateboard, Board, 600
9, WA, Skateboard, Shoes, 100
9, WA, Skateboard, Helmet, 200
10, WA, Skateboard, Board, 200
11, OR, Bicycle, Wheel, 50
11, OR, Bicycle, Helmet, 75
11, OR, Bicycle, Brake, 120
];

Re: Compare AGGR Expression to Embedded AGGR

Brett_Bleess — Wed, 19 Feb 2020 16:54:47 GMT

Jason, have a look at the following Design Blog post, it may be helpful on this one:

https://community.qlik.com/t5/Qlik-Design-Blog/Pitfalls-of-the-Aggr-function/ba-p/1463275

Here is the base URL to the site if you want to do some further searching for other posts on AGGR too, I grabbed the most likely one to help, but there are others too!

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

topic Re: Compare AGGR Expression to Embedded AGGR in QlikView

Compare AGGR Expression to Embedded AGGR

Re: Compare AGGR Expression to Embedded AGGR