Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to match the product name with an expression value

Hello all,

I'm hoping someone can help me out with something I've been struggling on. I am trying to get the name in a text box of the product with the worst variance year to date against budget. I can get the variance using the formula below. This basically looks at each month in the year and if the current month is greater or equal to it, it sums it and then compares it against actuals. The aggr groups it by brand and then the min takes the smallest one. The formula returns -3.18 which is the largest variance of the brands but I need it to return the brand's name. I'm thinking the match() function could be of use here but I can't get it to work... any thoughts?

=min(

aggr((sum({<Scenario={'$(vCscenario)'},M={'S'}>}Salesm)/1000000)-

((sum({<M={'S'},s.Month={1}>}Salesm)/1000000 

+ if($(vMaxMonth)>1,sum({<M={'S'},s.Month={2}>}Salesm)/1000000,0) 

+ if($(vMaxMonth)>2,sum({<M={'S'},s.Month={3}>}Salesm)/1000000,0)

  + if($(vMaxMonth)>3,sum({<M={'S'},s.Month={4}>}Salesm)/1000000,0)

    + if($(vMaxMonth)>4,sum({<M={'S'},s.Month={5}>}Salesm)/1000000,0)

      + if($(vMaxMonth)>5,sum({<M={'S'},s.Month={6}>}Salesm)/1000000,0)

        + if($(vMaxMonth)>6,sum({<M={'S'},s.Month={7}>}Salesm)/1000000,0)

          + if($(vMaxMonth)>7,sum({<M={'S'},s.Month={8}>}Salesm)/1000000,0)

           + if($(vMaxMonth)>8,sum({<M={'S'},s.Month={9}>}Salesm)/1000000,0)

             + if($(vMaxMonth)>9,sum({<M={'S'},s.Month={10}>}Salesm)/1000000,0)

               + if($(vMaxMonth)>10,sum({<M={'S'},s.Month={11}>}Salesm)/1000000,0)

                 + if($(vMaxMonth)>11,sum({<M={'S'},s.Month={12}>}Salesm)/1000000,0))),Brand))

0 Replies