% change

[#1]

hi,

I have a small requirement, I need to show count of products that falls in a a certain bucket % change (>.5% and <-.5%) for given a dimension 1, but the challenge is show calculate the % at one level below dimension 1.

consider the example as shown below

Category	Product	yesterday's price	Today's price	% change
Cat1	prod1	100	102	2.00%
Cat1	prod2	106	107	0.94%
Cat1	prod3	110	108	-1.82%
Cat1	prod4	123	122	-0.81%
Cat2	prod5	93	91	-2.15%
Cat2	prod6	101	102	0.99%
Cat2	prod7	27	28.5	5.56%

as you can see there are two categories cat1 and cat2 having multiple products within same category prod 1 to prod7, i need to calcuate % change at prod level but o/p table should only how count of products that falls in a a certain bucket % change (>.5% and <-.5%)

Category	prod count > .5%	prod count < .5%
Cat1	2	2
cat2	2	1

please let me know how to achive this.

thanks,

[marcus_sommer]

I think this is helpful: Buckets

- Marcus

[maxgro]

sum(if(aggr(sum([% change]), Product)<0.5,1))

sum(if(aggr(sum([% change]), Product)>0.5,1))