03 June 2012

Qlikview: Aggregation 3: number of MAXIMUMs in a dimension field

Scenario:
We have a data-set containing the market price of some commodities. And we need to find:
  1. The months in which the maximum market price of each commodity is reached
  2. How many commodities have reached their maximum market price in each month? (e.g. if only commodity A and commodity C have reached their maximum market price in April, then it is 2 for April)

Solution:
  1. Finding the maximum price for each commodity is easy, we will just create a straight table with COMMODITY field as dimension and MAX (PRICE) as expression. However, our aim is to find the months in which the maximum market price of each commodity is reached. So we will add a second expression as below:
             =MaxString(if(Price=aggr(NODISTINCT Max(Price), Commodity),Mth))
       
  2. The solution of item 1 will lead us to a solution for item 2. We just need the count of the COMMODITYs which are associated with the MONTHs returned by the second expression used in the solution of item 1.  So, if we use the AGGR function as follows, we can count the COMMODITYs:

    dimension:  =aggr(MaxString(if(Price=aggr(NODISTINCT Max(Price), Commodity),Mth)),Commodity)

    expression:  =count (Distinct Commodity)

Here is the implemented solution:




No comments:

Post a Comment