% increase, % decrease

[#1]

hi,

I have a small requirement, I need to show max % increase and % decrease 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
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 % increase and decrease at prod level but o/p table

should only max % increase and min % decrease within category.

Category	max % increase	min % decrease
Cat1	2.00%	-1.82%
cat2	5.56%	-2.15%

please let me know how to achieve this.

thanks,

[#2]

note: I'm deriving the % change column within qv, there is no direct column from database.

[petter · Accepted solution]

If you make a straight table with one dimension: Category and have two expressions like this:

Max( Aggr( Sum( [Today's price]/[yesterday's price] - 1 ) , Product ) )

Min( Aggr( Sum( [Today's price]/[yesterday's price] - 1 ) , Product ) )

You will get your max increase and min decrease

[#4]

thank you Peter it worked like a charm

[#5]

hi Petter thx for helping..now I have a new request where I need to show count of product where the % change > 1%  I tried to apply similar logic

count(if(Aggr( Sum( [Today's price]/[yesterday's price] - 1 ) , Product )>=.001,Product)) but failed..can you let me know how this works.

[petter]

I see some problems with your nesting of the brackets. Furthermore you cant have an if in front of the Aggr-function unless it only returns a single value

[#7]

hmm..it was just a psedo code only..thats the issue it was returning only 1 value any other way of achiveving this..other than using aggr function

[petter]

-Sum( Aggr( Sum([Today's price]/[yesterday's price] - 1) , Product) > 0.01)

Will work for you finding all that are above 1%.