Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor
Contributor

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