Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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
note: I'm deriving the % change column within qv, there is no direct column from database.
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
thank you Peter it worked like a charm
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.
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
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
-Sum( Aggr( Sum([Today's price]/[yesterday's price] - 1) , Product) > 0.01)
Will work for you finding all that are above 1%.