5 Replies Latest reply: Jun 13, 2017 4:37 AM by Глеб Аитов RSS

    Transform selections in the field by a common rule

    Глеб Аитов
    Глеб Аитов Jun 8, 2017 8:59 AM

      Hi,

       

      I have the following data:

      Product

      Alpha 100

      Beta 200
      Theta 300
      Gamma 400

       

      I have set a new $(vPY) variable, which takes the selected field, subtracts certain number (say, 15) from the numeric part and returns the value. In set analysis I then use this variable as in the following example: SUM ( {< Product={ "$(=$(vPY) )" } >} Sales)

       

      Products selected$(vPY)

      Alpha 100

      		Alpha 85
      Beta 200Beta 185
      Theta 300Theta 285
      Gamma 400Gamma 385

       

      Question 1: when multiple fields are selected, it returns nothing. What i need it to return is as follows, so i could continue to use it in set analysis:

       

      Products selected$(vPY)
      Alpha 100, Beta 200Alpha 85, Beta 185

       

      Question 2: when nothing is selected in the FIELD, the variable returns nothing. Effectively the formula gives the following result SUM ( {< Product={ } >} Sales), and thus returns zero sales. I need to tell Qlik to disregard the Product dimension, when nothing is selected in the Product field.


      So far I managed to do it with the IF statement, but this is clumsy (if(GetSelectedCount(Product)=0, SUM (Sales), SUM ( {< Product={ "$(=$(vPY) )" } >} Sales)

       

       

      Thank you!