Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

% increase, % decrease

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

     

CategoryProductyesterday's priceToday's price
Cat1prod11001022.00%
Cat1prod21061070.94%
Cat1prod3110108-1.82%
Cat1prod4123122-0.81%
Cat2prod59391-2.15%
Cat2prod61011020.99%
Cat2prod72728.55.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.

   

Categorymax % increasemin % decrease
Cat12.00%-1.82%
cat25.56%-2.15%

please let me know how to achieve this.

thanks,

1 Solution

Accepted Solutions
petter
MVP
MVP

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

View solution in original post

7 Replies
Not applicable
Author

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

petter
MVP
MVP

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

View solution in original post

Not applicable
Author

thank you Peter it worked like a charm

Not applicable
Author

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
MVP
MVP

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

Not applicable
Author

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
MVP
MVP

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

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